Skip to main content

Conditional Formatting Formula Help!

  • June 7, 2019
  • 1 reply
  • 27 views
K

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!

    1 reply

    JonathanBowen
    Forum|alt.badge.img+18

    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

    Justin_Barrett
    Terms & ConditionsAccessibility statement