Question 393944 in Javascript Question posted in
A very good W3school tutorial can be found here.

How to sort out this logical mess with my javascript?

I have a Google Sheet and I’m trying to output a date or string depending on three dates and three booleans which represent a task as complete.

I’ve been writing a custom function to do this but I can’t get the logic to work out, I’ve been working on it for days now.

In my sheet, I have an initial date. The three dates the tasks are due are calculated in columns D, F and H. Then there are three ‘done’ booleans that represent whether a task has been completed.

I want this to reduce down to a single output where a date is given for the ‘next date a task is due’, or if the last task is done, then mark it as ‘DELETE’. The complicated part comes when a later task takes priority over a task not completed on an earlier date.

But the logic is too hard. I can’t seem to break it down to a way I can solve how to do it.

In my sheet I have put the expected outputs for each case. (I’m aware I haven’t even sampled all 64 cases, but I daren’t mess with too much at this point).

So essentially, I need a function to take the dates and dones and then get the result.

function myFunction(date1,date2,date3,done1,done2,done3) {
  
  var TODAY = new Date();

  DATES = [date1,date2,date3]

  var wait = []

  for (var k of [date1,date2,date3]){
    if (k > TODAY){
      wait.push(1)
    }else{
      wait.push(0)
    }
  }

  var done = []

   for (var k of [done1,done2,done3]){
    if (k){
      done.push(1)
    }else{
      done.push(0)
    }
  }

  var omega = []
  for (var i = 0; i < wait.length; i++) {
    omega.push(done[i] | wait[i])
  }

As you can see, my last attempt included doing an OR function on then and taking the rightmost zeros, but ignore that because it doesn’t work.

Anyone know how I should try this in a way that works?

Tags:

2

Answers


  1. 0

    Try something like this.

    function getNextDueDate(date1, date2, date3, done1, done2, done3) {
  const today = new Date();
  const dates = [new Date(date1), new Date(date2), new Date(date3)];
  const dones = [done1, done2, done3];

  // Filter for dates that are not done and are still in the future
  const futureDates = dates.filter((date, index) => !dones[index] && date > today);

  // Find the earliest date from the filtered future dates
  const nextDueDate = futureDates.reduce((earliest, date) => {
    return date < earliest ? date : earliest;
  }, new Date('2999-12-31')); // initial date far in the future

  // If the nextDueDate year is still in the far future then all tasks are done (on no dates)
  return (nextDueDate.getFullYear() === 2999) ? 'DELETE' : nextDueDate.toLocaleDateString(); // Returns the next due date in a readable format
}

// Example Usage
console.log(getNextDueDate('2024-06-12', '2024-06-24', '2024-07-04', false, false, false));
    Login or Signup to reply.
  2. 0

    Try this adapted version of @mplungjan ‘s code

    Removed the reference to today’s date in the date filter and added a ternary operator to check if task3 was done.

    function myFunction(date1, date2, date3, done1, done2, done3) {
  const today = new Date();
  const dates = [new Date(date1), new Date(date2), new Date(date3)];
  const dones = [done1, done2, done3];

  // Filter for dates that are not done
  const futureDates = dates.filter((date,index) => !dones[index]);

  // Use 2999 if final task is complete, otherwise reflect the earliest date from the filtered dates
  const nextDueDate = done3 !== '' ? new Date('2999-12-31') : futureDates.reduce((earliest, date) => {
    return date < earliest ? date : earliest;
  }, new Date('2999-12-31')); // initial date far in the future

  // If the nextDueDate year is still in the far future then all tasks are done (on no dates)
  return (nextDueDate.getFullYear() === 2999) ? 'DELETE' : nextDueDate.toLocaleDateString(); // Returns the next due date in a readable format
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search