Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Help! Trouble with date formulas #ERROR! and NaN

Solved
Jump to Solution
1715 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.

Spruce
6 - Interface Innovator
6 - Interface Innovator

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

Devon
Spruce
6 - Interface Innovator
6 - Interface Innovator

@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