Formula that adds "not due yet" if a due date is a certain time in the future #support:formulas

[Volunteer_SED]

Hello, I have a due date column and I would like to use a formula that adds “not due yet” in an adjacent column if the date is more than 6 weeks in the future. Any advice? #support:formulas

[JonathanBowen]

Hi @Volunteer_SED - you can do something like this:

Where the status field is:

IF({Due Date} > DATEADD(TODAY(), 6, 'weeks'), 'not due yet')

[Volunteer_SED]

Thank you!

So I already have this formula in the column and I wand to add the ‘not due yet’ function. I have tried adding it at the end, but it seems to accept it (no error comes up), but then it does not work and when I look back at the formula the bit I have added is no longer there.

Any tips on how I can write this properly, including the ‘not due yet’ function?

IF(AND(DATETIME_DIFF({Next KC vacc due},TODAY(),‘weeks’) >= 1,DATETIME_DIFF({Next KC vacc due},TODAY(),‘weeks’) <= 6),“ :stopwatch: Due Soon​:stopwatch:”,IF(IS_BEFORE({Next KC vacc due},TODAY()), “ :question: Past due date​:question:”))

[JonathanBowen]

OK, if you have other timeframes and messages in there, I would approach it a slightly different way. Generally, you want to cover all of the scenarios, It looks like you’ve got between 1 and 6 weeks, earlier than today, i.e. past, and now more than 6 weeks, but maybe not less than 1 week, but later than today. To get the right level of granularity I would move this to ‘days’ rather than weeks (although this might not be strictly necessary). I would do something like this:

Where Date Diff is:

DATETIME_DIFF({Due Date}, TODAY(), 'days')

and Status is:

IF(
  {Date Diff} < 0, 
  'Past due',
  IF(
    {Date Diff} = 0,
    'Due Today',
    IF(
      {Date Diff} < 7,
      'Due This Week',
      IF(
        {Date Diff} < 42,
        'Due Soon',
        'Not due yet'
      )      
    )    
  )
)