Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

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

Topic Labels: Formulas
Solved
148 2
cancel
Showing results for
Did you mean:  4 - Data Explorer

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)

1 Solution

Accepted Solutions  10 - Mercury

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.

2 Replies 2  10 - Mercury

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.  4 - Data Explorer

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