Help

Nested if for dates

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

Hi,

I have a table to have a list of equipments and when they were cleaned. Apart from equipment details I have these fields:

  1. Last maint date (a date field - to be manually entered)
  2. Next maint date (formula which returns a date 120 days after Last maint date)
  3. Actual cleaned date (date field - date on which an equipment was actually cleaned - to be manually entered)
  4. Status (Formula - I want this to be evaluated for 3 conditions and return text phrases accordingly)

Conditions:
If the 'Actual cleaned date' is greater than 'Next maint date' then return 'Delayed' in Status
If the 'Actual cleaned date' is equal to 'Next maint date', then return 'Cleaned on time'
If the 'Actual cleaned date' is lesser than 'Next maint date', then return 'Cleaner early'  

I tried this nested if 

IF({Actual date cleaned}>{Next maint date},"Delayed",IF({Actual date cleaned}={Next maint date},"Cleaned on time"))
But it is not returning any phrase as needed.

Kindly help
2 Solutions

Accepted Solutions
harimonthstayz
5 - Automation Enthusiast
5 - Automation Enthusiast

@Arthur_Tutt thank you for the response and all fields are in date format.

The mistake I did was, I had not included If({Actual cleaned date}<{Next maint date},'Cleaned Early'). I included this in the nested if and the text phrases started showing up.

However, now I see that if actual cleaned date is blank, the status field is showing text 'Cleaned early' by default. So I tried inserting one more if condition using blank(). Please see screenshot. Although all other three conditions Cleaned early, Cleaned on time and Delayed are working, the condition when actual cleaned date is blank is not working. Would be great to receive a solution for this. Thanks again 

Nested if blank.png

 

See Solution in Thread

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

So close @harimonthstayz ! I made a minor modification and put the check for blanks at the beginning of the logic (otherwise will never execute). Here you go:

IF(NOT({Actual cleaned date}), 'Enter actual cleaned date', IF({Actual cleaned date} > {Next maint date}, 'Delayed', IF({Actual cleaned date} = {Next maint date}, 'Cleaned On Time', IF({Actual cleaned date} < {Next maint date}, 'Cleaner Early'))))

Screenshot 2023-10-28 232718.png

Screenshot 2023-10-28 232731.png

See Solution in Thread

5 Replies 5
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hey @harimonthstayz ! Your nested IF statement looks good to me. Are you sure all the data fields are still in date format? I ran this example and it worked for me: 

IF({Actual cleaned date} > {Next maint date}, 'Delayed', IF({Actual cleaned date} = {Next maint date}, 'Cleaned On Time', IF({Actual cleaned date} < {Next maint date}, 'Cleaner Early')))

Screenshot 2023-10-28 222000.pngScreenshot 2023-10-28 222356.png

harimonthstayz
5 - Automation Enthusiast
5 - Automation Enthusiast

@Arthur_Tutt thank you for the response and all fields are in date format.

The mistake I did was, I had not included If({Actual cleaned date}<{Next maint date},'Cleaned Early'). I included this in the nested if and the text phrases started showing up.

However, now I see that if actual cleaned date is blank, the status field is showing text 'Cleaned early' by default. So I tried inserting one more if condition using blank(). Please see screenshot. Although all other three conditions Cleaned early, Cleaned on time and Delayed are working, the condition when actual cleaned date is blank is not working. Would be great to receive a solution for this. Thanks again 

Nested if blank.png

 

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

So close @harimonthstayz ! I made a minor modification and put the check for blanks at the beginning of the logic (otherwise will never execute). Here you go:

IF(NOT({Actual cleaned date}), 'Enter actual cleaned date', IF({Actual cleaned date} > {Next maint date}, 'Delayed', IF({Actual cleaned date} = {Next maint date}, 'Cleaned On Time', IF({Actual cleaned date} < {Next maint date}, 'Cleaner Early'))))

Screenshot 2023-10-28 232718.png

Screenshot 2023-10-28 232731.png

harimonthstayz
5 - Automation Enthusiast
5 - Automation Enthusiast

@Arthur_Tutt instead of having the blank condition at the last, I brought it to be the first condition. It seems to work now. Please refer the screen shots. Thanks for the help, much appreciated. 

If condition.png

Status.png

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Glad I could help @harimonthstayz ! Can you do me a quick favor and mark my post as a solution (can have more than one). Helps build my credibility in the community. Thanks!