Skip to main content
Solved

Help! Trouble with date formulas #ERROR! and NaN

Spruce
Forum|alt.badge.img+7
  • Spruce
  • Known Participant
  • 11 replies

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

Best answer by Databaser

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. 

View original
Devon
Did this topic help you find an answer to your question?

4 replies

Databaser
Forum|alt.badge.img+19
  • Databaser
  • Inspiring
  • 866 replies
  • Answer
  • October 16, 2023

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. 

Jan - Databaser
Spruce
1 person likes this
  • Spruce
    Spruce
kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6002 replies
  • October 16, 2023

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
1 person likes this
  • Spruce
    Spruce
Spruce
Forum|alt.badge.img+7
  • Spruce
  • Author
  • Known Participant
  • 11 replies
  • October 17, 2023
Databaser wrote:

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.

Devon
Databaser
1 person likes this
  • Databaser
    Databaser
Spruce
Forum|alt.badge.img+7
  • Spruce
  • Author
  • Known Participant
  • 11 replies
  • October 17, 2023
kuovonne wrote:

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!

Devon

Reply


Most helpful members this week

Terms & Conditions