Skip to main content

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!

Does this look right?

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?

Does this look right?

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.

Does this look right?

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?


Hi

I'm having a similar issue and your formula solution didn't work for me.  Not sure what I'm missing my only difference was that I was also using the Time portion as well in my Time In/Out fields.

I also would like to remove the NaN from my list of outputs.  Lastly, I believe the question about the Sum and Infinity was at the bottom when you click on the different column options it seems that you can't get a some from this calculation function of time 

Here was my original formula, but I'm not sure how to add the If() to remove the NaN

DATETIME_DIFF(({Time Out}), {Time In}, 'minutes')
Thanks!!
 

Hi

I'm having a similar issue and your formula solution didn't work for me.  Not sure what I'm missing my only difference was that I was also using the Time portion as well in my Time In/Out fields.

I also would like to remove the NaN from my list of outputs.  Lastly, I believe the question about the Sum and Infinity was at the bottom when you click on the different column options it seems that you can't get a some from this calculation function of time 

Here was my original formula, but I'm not sure how to add the If() to remove the NaN

DATETIME_DIFF(({Time Out}), {Time In}, 'minutes')
Thanks!!
 

Hmm, does this look right?

IF(
AND(
{Time Out},
{Time In}
),
DATETIME_DIFF({Time Out}, {Time In}, "minutes")
)

 

Reply


Terms & Conditions