Need Formula for Done Early, Done Late, and Done On Time

Question

Hi there - I need help building a formula that tracks if tasks have been completed early, on time, or late.

I have a column where I mark a task complete, so it will have to start with If status = complete, etc. I then have a column each for “Start Date” and “End Date” (projected end date). Then a separate column for “Completion Date” (actual end date).
I cannot for the life of me get all the parentheses right, this is what I have, but it’s not functioning correctly:
IF(
{Status}=“Complete”
IF(
IS_BEFORE({completion date}, {end date}),
“Done Early”,
IF(
IS_SAME({completion date}, {end date}),
“Done On Time”,
IF(
IS_AFTER({COMPLETION DATE}, {END DATE}),
“Done Late”
)
)
)
)
Any help would be appreciated! (Simplified example attached)

augmented · Accepted Answer

Hi Claire. Let me try to lay it out for you.

IF(Status = 'COMPLETE',
   IF(IS_BEFORE({Completion Date}, {End Date}), 'Early',
       IF(IS_AFTER({Completion Date}, {End Date}), 'Late',
           IF(IS_SAME({Completion Date}, {End Date}, 'days'), 'On Time'
           )
       )
   )
)

Case matters when checking the value of the Status field. Also, problems can be avoided in Airtable formulas by breaking the habit of using double quotes. If you copy a formula from somewhere, go ahead and replace the double quotes with singles. JMHO.

Claire_Selves · Answer

That absolutely worked, thank you so much! I’m very new to figuring these formulas out, I’ll keep that in mind going forward!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded