Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 16, 2023 09:14 AM - edited Oct 16, 2023 09:15 AM
Hello! I'm slowly learning how to use date formulas, but I have hit a wall in my ability to troubleshoot.
I have two date formulas causing me trouble.
1) My Rush Due Date is displaying #ERROR! when "Rush" checkbox is not checked.
DATETIME_FORMAT(IF(Rush, WORKDAY(Created, 4, '2023-11-11, 2023-12-25, 2024-01-01, 2024-02-19, 2024-03-29, 2024-05-20'), ""), 'LL')
2) My "Time Since Ready for Pickup" formula is displaying NaN when a row is not yet "Ready for Pickup"
IF(Completed, "Completed", DATETIME_DIFF( NOW(),{Ready for Pickup Timestamp}, 'days'))
Are there any heros out there who can help me out?
Thank you so much in advance!
Devon
Solved! Go to Solution.
Oct 16, 2023 10:31 AM
1. You're trying to datetime format an empty value "" . Try:
IF(Rush, DATETIME_FORMAT(WORKDAY(Created, 4, '2023-11-11, 2023-12-25, 2024-01-01, 2024-02-19, 2024-03-29, 2024-05-20'), 'LL'), "")
2. Add another IF() statement so your field will stay empty when {Ready for Pickup Timestamp} is empty, to avoid NaN
IF(Completed, "Completed", IF({Ready for Pickup Timestamp}, DATETIME_DIFF( NOW(),{Ready for Pickup Timestamp}, 'days')))
Let me know if that works.
Oct 16, 2023 10:36 AM
DATETIME_FORMAT() is expecting a date as an input. But you are only providing a date if {Rush} is selected. So you need to change your nesting. Have your outermost formula be the IF() and nest DATETIME_FORMAT() inside the IF().
For your second formula, DATETIME_DIFF() expects to date/time values as inputs. But {Ready for Pickup Timestamp} doesn't have a value for several records, so DATETIME_DIFF() results in NaN (not a number) for those records. To leave this blank, add checking for a value in {Ready for Pickup}.
By the way, even though your second formula appears to produce the number 1 in the last record in your screen shot, that number is really a text string. This is because your formula sometimes produces a text string ("Completed"). I also do not like using NOW() in formula as it is resource intensive, especially if you have a lot of records. Using TODAY() is a little better, although I don't like it much either.
Oct 16, 2023 10:31 AM
1. You're trying to datetime format an empty value "" . Try:
IF(Rush, DATETIME_FORMAT(WORKDAY(Created, 4, '2023-11-11, 2023-12-25, 2024-01-01, 2024-02-19, 2024-03-29, 2024-05-20'), 'LL'), "")
2. Add another IF() statement so your field will stay empty when {Ready for Pickup Timestamp} is empty, to avoid NaN
IF(Completed, "Completed", IF({Ready for Pickup Timestamp}, DATETIME_DIFF( NOW(),{Ready for Pickup Timestamp}, 'days')))
Let me know if that works.
Oct 16, 2023 10:36 AM
DATETIME_FORMAT() is expecting a date as an input. But you are only providing a date if {Rush} is selected. So you need to change your nesting. Have your outermost formula be the IF() and nest DATETIME_FORMAT() inside the IF().
For your second formula, DATETIME_DIFF() expects to date/time values as inputs. But {Ready for Pickup Timestamp} doesn't have a value for several records, so DATETIME_DIFF() results in NaN (not a number) for those records. To leave this blank, add checking for a value in {Ready for Pickup}.
By the way, even though your second formula appears to produce the number 1 in the last record in your screen shot, that number is really a text string. This is because your formula sometimes produces a text string ("Completed"). I also do not like using NOW() in formula as it is resource intensive, especially if you have a lot of records. Using TODAY() is a little better, although I don't like it much either.
Oct 16, 2023 05:54 PM
@Databaser , you're amazing! Thank you so much for the quick reply. Both formulas work great.
Oct 16, 2023 05:54 PM
@kuovonne , thank you so much for chiming in with a great explanation and the hot tip about the TODAY instead of NOW. I have implemented your tip 🙂 Thank you!