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

[Claire_Selves]

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 solution]

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]

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