Help

Help on calculation between dates (and removing NaN)

Topic Labels: Formulas
524 2
cancel
Showing results for 
Search instead for 
Did you mean: 
ArtoReinvention
4 - Data Explorer
4 - Data Explorer

Hello, I am stuck on something that seems simple, and I've tried navigating the answer here, but still can't get my formula to work.

I have two fields I'm working with, a check-in field, which is a dd/mm/yyyy field, and a check-out which is also a dd/mm/yyyy field.  I don't always use both fields, though - sometimes one is blank, and sometimes both are blank.

I'm trying to calculate the # of nights between the two dates, and the formula is working fine, but returns "NaN" when there is no date.  How can I correct the formula to simply leave that field empty if there are no dates? 

Also, it seems to be wanting to calculate a sum, which I don't want it to do.  Not sure why the Sum and Infinity symbols? 

Here is my formula: 

DATETIME_DIFF({Check-out}, {Check-in}, "days")

Thank you!

2 Replies 2

Does this look right?

Screenshot 2024-07-28 at 1.07.28 PM.png

IF(
  AND(
    {Check-out},
    {Check-in}
  ),
  DATETIME_DIFF({Check-out}, {Check-in}, "days")
)

---
Not sure I'm following about the sum, infinity symbols bit though, could you tell me more about that?

YES!  Thank you so much!  That worked.  Appreciate it.