Skip to main content

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

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. 

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.

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. 


@Databaser , you're amazing! Thank you so much for the quick reply. Both formulas work great.

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.


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

Reply


Terms & Conditions