Past Due Date

PMOGal

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.

PMOGal

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

ericncarr

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

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

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

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

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

David G

@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?