Need to return "days overdue" if "Completion date" is blank

Melody G

Hi there,

I'm trying to show the number of days a project is overdue based on the Expected Date of Completion versus today's date, if the Date Repair Completed is blank or has text instead of a date in it (i.e. Cancelled).

I didn't know after searching if I could list if a "NOT date" so here is the best I could come up with and couldn't find a solution after about a half an hour of searching. I'd appreciate any help! I would be willing to use ISBLANK if needed and I can maybe filter out date cells that contain text.

I tried =IF(NOT(ISDATE([Date Repair Completed]@row, [Expected Date of Completion]@row - TODAY())), ""))

Melody G

I realize I intended to say I wanted to leave the cells blank if the Date Repair Completed had text in it -- so I want it to do this formula if there is not a date in the field or if there is text in the field, otherwise leave it blank. Sorry!

Kelly Moore

Hey @Melody G

Is there a formula that you also need for when there is a date in the [Date Repair Completed]? I'll take a guess:

=IF(ISDATE([Date Repair Completed]@row), [Date Repair Completed]@row - [Expected Date of Completion]@row, IF(ISTEXT([Date Repair Completed]@row), "", [Expected Date of Completion]@row - TODAY()))

Will this work for you?

Kelly

Melody G

Hi there, thank you so much! Actually, I only want it to return a formula result if there is no date (work is not completed) and then to return BLANK if there is text in the field (if someone Typed "cancelled" in that field, for instance.) I hope that clarifies a bit!

However, I think what you gave me works perfectly, thank you so again!

Kelly Moore

Hey @Melody G

=IF(OR(ISDATE([Date Repair Completed]@row),ISTEXT([Date Repair Completed]@row)), "", [Expected Date of Completion]@row - TODAY())

Try this

Kelly