- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 28, 2023 04:09 AM
Hi,
I have a table to have a list of equipments and when they were cleaned. Apart from equipment details I have these fields:
- Last maint date (a date field - to be manually entered)
- Next maint date (formula which returns a date 120 days after Last maint date)
- Actual cleaned date (date field - date on which an equipment was actually cleaned - to be manually entered)
- 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
Kindly help
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 28, 2023 08:12 PM
@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
 
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 28, 2023 08:29 PM
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'))))
 
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 28, 2023 07:27 PM
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')))
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 28, 2023 08:12 PM
@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
 
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 28, 2023 08:29 PM
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'))))
 
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 28, 2023 08:34 PM
@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.
 
 
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Oct 28, 2023 08:51 PM
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!
