If date completed is later/before/on date then enter text

Chelsea Duncan

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!

Jason Duryea

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

Jason Duryea

Try this formula

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

Chelsea Duncan

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

Jason Duryea

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.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Chelsea Duncan

@Jason Duryea

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?

Jason Duryea

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”)))

Chelsea Duncan

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([h-analysis date completed]@row >[h-analysis due date]@row, “Late”, IF([h-analysis date completed]@row <= [h-analysis due date]@row, “On Time”)

Adjusted for the blank date completed, formula is:

=IF(AND(ISBLANK([H-analysis date completed]@row), [H-analysis due date]@row < TODAY()), "Late", IF([H-analysis date completed]@row >[H-analysis due date]@row, “Late”, IF([H-analysis date completed]@row <= [H-analysis due date]@row, “On Time”)))

Any ideas why it's not working?

Thank you

Chelsea Duncan

I've been known to be a little fancy lol.

This is what worked and I have no idea why:

=IF(AND(ISBLANK([H-Analysis Date Completed]@row), [H-Analysis Due Date]@row < TODAY()), "Late", IF([H-Analysis Date Completed]@row > [H-Analysis Due Date]@row, "Late", IF([H-Analysis Date Completed]@row <= [H-Analysis Due Date]@row, "On Time")))

I would love to know why this one worked cause the one you did returned unpareseable.

Thank you.

Jason Duryea

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

Chelsea Duncan

ok, thank you!

Rachael Stammers

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