Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

Topic Labels: Formulas
591 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

3 Replies 3

Hi @Volunteer_SED - you can do something like this:

Screenshot 2020-07-17 at 14.51.27

Where the status field is:

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

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:”))

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:

Screenshot 2020-07-21 at 17.00.17

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'
      )      
    )    
  )
)