Help

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

Solved
Jump to Solution
1178 0
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!