Skip to main content
Solved

Nested if for dates

  • October 28, 2023
  • 5 replies
  • 26 views
H
Forum|alt.badge.img+8

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

Best answer by harimonthstayz

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')))


@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 

 

 

5 replies

Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • October 29, 2023

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')))

H
H
Forum|alt.badge.img+8
  • harimonthstayz
  • Author
  • Inspiring
  • Answer
  • October 29, 2023

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')))


@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 

 

 

Arthur_Tutt
Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • October 29, 2023

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'))))

 

H
Forum|alt.badge.img+8
  • harimonthstayz
  • Author
  • Inspiring
  • October 29, 2023

@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. 

 

 

Arthur_Tutt
Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • October 29, 2023

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! 

H
Terms & ConditionsAccessibility statement