If date completed is later/before/on date then enter text
This seems like it would be a really easy formula but I can't find anything for it.
If [date completed]1 is later than [deadline]1 then enter "Late"
if [date completed]1 is before or on [deadline]1 then enter "On Time"
Thanks so much!
Best Answer

The only difference I see are the column names that you used, which I believe are case sensitive...maybe that is the difference.
Answers

Try this formula
=IF([date completed]@row >[deadline]@row, “Late”, IF([date completed]@row <= [deadline]@row, “On Time”))

Yep, it worked. And of course, simple. Why do you use @row vs the actual row number?

Using @row is considered best practice and is supposed to improve performance of the formula calculations, and make loading/saving sheets faster.
It also makes it easy to convert the cell formula to a column formula.

There's something with this that I didn't consider. The formula still returns "on time" even if there is no date completed entered, which means it could still be in work when it's overdue. Can I have it to where it enters Late if the analysis due date has passed and there is no date in analysis date completed?

Now you're getting fancy...😀
You would need to add another condition to the nested IF statement.
AND(ISBLANK([date completed]@row), [deadline]@row < TODAY())
It would look like this...
=IF(AND(ISBLANK([date completed]@row), [deadline]@row < TODAY()), "Late", IF([date completed]@row >[deadline]@row, “Late”, IF([date completed]@row <= [deadline]@row, “On Time”)))

Lol, I am a bit fancy!
So I'm getting unparseable. To summarize: If the analysis due date is in the past, and the date completed is blank, then "Late". If the analysis due date and the date completed is in the past then "Late". If the date completed is equal to or before the date completed, then "On Time".
To compare, the previous formula, which worked, is:
=IF([hanalysis date completed]@row >[hanalysis due date]@row, “Late”, IF([hanalysis date completed]@row <= [hanalysis due date]@row, “On Time”)
Adjusted for the blank date completed, formula is:
=IF(AND(ISBLANK([Hanalysis date completed]@row), [Hanalysis due date]@row < TODAY()), "Late", IF([Hanalysis date completed]@row >[Hanalysis due date]@row, “Late”, IF([Hanalysis date completed]@row <= [Hanalysis due date]@row, “On Time”)))
Any ideas why it's not working?
Thank you

I've been known to be a little fancy lol.
This is what worked and I have no idea why:
=IF(AND(ISBLANK([HAnalysis Date Completed]@row), [HAnalysis Due Date]@row < TODAY()), "Late", IF([HAnalysis Date Completed]@row > [HAnalysis Due Date]@row, "Late", IF([HAnalysis Date Completed]@row <= [HAnalysis Due Date]@row, "On Time")))
I would love to know why this one worked cause the one you did returned unpareseable.
Thank you.

The only difference I see are the column names that you used, which I believe are case sensitive...maybe that is the difference.

ok, thank you!

Hi Everyone,
I'm struggling with a similar formula coming back as #INCORRECT ARGUMENT :(
My column Names are shown in the image
And the formula I'm using below:
=IF(AND(ISBLANK([F2 Mitigation Completion Date]@row, [F 2 Mitigation Due Date]@row < TODAY()), "Late", IF([F2 Mitigation Completion Date]@row > [F 2 Mitigation Due Date]@row, "Late", IF([F2 Mitigation Completion Date]@row <= [F 2 Mitigation Due Date]@row, "On Time"))))
All help greatly appreciated
Help Article Resources
Categories
Check out the Formula Handbook template!