Past Due Date

Options

My Smartsheet has an "Estimated Due Date" column in date format. To the right of it, I have a column "Past Due Date". Based on Today's date, I'd like the Estimated Due Date to be compared with Today's Date. If the Estimated Due Date is in the past, I'd like a "Y" or "Yes" to be returned in the Past Due Date column.

Is there a formula for this? Thank you.

Answers

  • PMOGal
    PMOGal ✭✭✭✭✭
    Options

    ... sorry, one more piece of criteria. And Status does not equal Closed.

  • ericncarr
    ericncarr ✭✭✭✭✭
    Options

    Here is the formula you can use:

    =IF(AND([Estimated Due Date]@row < TODAY(), Status@row <> "Closed"), "Yes", "No")

    AND can be used to define two separate criteria that must be satisfied.

    Today() will return today's date so that you can compare it against other dates.

    Personally, I'd make the Past Due column a checkbox column and use this formula but it depends on how you want to report out on it etc.

    =IF(AND([Estimated Due Date]@row < TODAY(), Status@row <> "Closed"), 1, 0)

  • PMOGal
    PMOGal ✭✭✭✭✭
    edited 08/03/22
    Options

    Hi Eric. Sorry, I forgot to mention that sometimes the Estimated Due Date is blank. Right now, with your formula (which works BTW), it returns a Yes or No if a date is supplied. If no date is supplied it is returning a Yes. Can you adjust the formula so that if the Estimated Due Date is blank, it returns nothing?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @PMOGal,

    Does this formula work more to your liking with having the blanks if the estimated date is blank:

    =IF(ISBLANK([Estimated Due Date]@row), " ", IF(AND([Estimated Due Date]@row < TODAY(), Status@row <> "Closed"), "Yes", "No")).

    Example output below:

    Hope this helps - if there is something I'm missing please let me know!

  • PMOGal
    PMOGal ✭✭✭✭✭
    Options

    Hi Nick. I am getting an #UNPARSEABLE error. My Estimated Due Date is a Date field. My Status is a Dropdown list. What do I need to change? Thanks for your help.

  • PMOGal
    PMOGal ✭✭✭✭✭
    Options

    Hi Nick .... correction. I found my error. Thank you for the help. AWESOME!

  • David G
    David G ✭✭
    edited 09/26/22
    Options

    @Nick Korna I have something similar however; I have a start date, end date and a complete column which is a checkbox. I want to have a widget in my dashboard that tells me which tasks are past due based on the complete column not being checked. Any formulas for this?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!