Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

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

Topic Labels: Formulas
Solved
Jump to Solution
1371 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Claire_Selves
4 - Data Explorer
4 - Data Explorer

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

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
augmented
10 - Mercury
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.

See Solution in Thread

2 Replies 2
augmented
10 - Mercury
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.

Claire_Selves
4 - Data Explorer
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!