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

# IF Formula - late date, current date, blank date

Topic Labels: Formulas
Solved
Jump to Solution
253 2
cancel
Showing results for
Search instead for
Did you mean:
5 - Automation Enthusiast

I’m currently using the following formula for tracking Project Health: IF({FOC (Current)}<TODAY(),“LATE!”,“ON TARGET”)

however there are times when the “FOC (Current)” date field is appropriately blank. Right now it’s labeling it as “Late” but I need it to show “No FOC” instead.

1. If FOC (Current) is < today = “LATE”
2. If FOC (Current) is =/> today = “On Target”
3. If FOC (Current) is blank = “No FOC”

I can’t seem to make the multi-IF’s to work. Thanks in advance!

1 Solution

Accepted Solutions
13 - Mars

Hi @Shawntel_Cote - you need a nested IF statement here:

``````IF(
NOT({FOC (Current)}),
'No FOC',
IF(
{FOC (Current)} < TODAY(),
'LATE',
'ON TARGET'
)
)``````

So we first check if {FOC Current} has no value or is blank. If it is blank, then return “No FCO”. If it isn’t blank, then check if the date is earlier than today - if so later, otherwise on target.

2 Replies 2
13 - Mars

Hi @Shawntel_Cote - you need a nested IF statement here:

``````IF(
NOT({FOC (Current)}),
'No FOC',
IF(
{FOC (Current)} < TODAY(),
'LATE',
'ON TARGET'
)
)``````

So we first check if {FOC Current} has no value or is blank. If it is blank, then return “No FCO”. If it isn’t blank, then check if the date is earlier than today - if so later, otherwise on target.

5 - Automation Enthusiast

works great, thank you!