Conditional Formatting Formula Help!

[Kathryn_Ruggeri]

Can someone help me figure out the correct formula for the following problem? I’d like to flag something as overdue if the due date/expiration date has passed. For example - I’m working with Rabies shot due dates for dogs. I can enter the due date in a column. I’d like to have a second column that flags that as “OVERDUE” if that rabies due date is before Today (Or - ideally - 1 week/month before today). Can someone help me figure out this formula?

Thanks!

[JonathanBowen]

Hi @Kathryn_Ruggeri - to highlight those records due on or before today you can use:

IF(DATETIME_DIFF(TODAY(), {Due Date}) >= 0, 'Overdue')

Or those that are overdue or due within 7 days, you can use:

IF(DATETIME_DIFF(TODAY(), {Due Date}, 'days') >= -7, 'Overdue')

If you wanted to make it a bit more user-friendly, you could add a checkbox to denote items completed (and therefore not relevant for a due date message):

The formula in this case is:

IF(
  AND(DATETIME_DIFF(TODAY(), {Due Date}, 'days') >= 0, NOT({Completed})),
  'Overdue', 
  IF(
    AND(DATETIME_DIFF(TODAY(), {Due Date}, 'days') > -7, NOT({Completed})),
    'Due soon', 
    ''
  )
)

(So, on or before today is “overdue”, within 7 days is “due soon”, unless the task is completed)

JB