Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Help! Trouble with date formulas #ERROR! and NaN

Solved
Jump to Solution
1336 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Spruce
6 - Interface Innovator
6 - Interface Innovator

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')
Spruce_0-1697472652432.png

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'))
Spruce_1-1697472791463.png

Are there any heros out there who can help me out?

Thank you so much in advance!
Devon

Devon
2 Solutions

Accepted Solutions
Databaser
12 - Earth
12 - Earth

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. 

See Solution in Thread

kuovonne
18 - Pluto
18 - Pluto

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.

See Solution in Thread

4 Replies 4
Databaser
12 - Earth
12 - Earth

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. 

kuovonne
18 - Pluto
18 - Pluto

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.

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

Devon

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

Devon