Skip to main content

Help on calculation between dates (and removing NaN)

  • July 27, 2024
  • 4 replies
  • 43 views
A
Forum|alt.badge.img+4

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!

4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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?

Adam, Airtable Consultant - thetimesaving.company
A
A
Forum|alt.badge.img+4

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.

TheTimeSavingCo
S
Forum|alt.badge.img+2
  • sfeery
  • New Participant
  • January 8, 2025

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!!
 
1 Attachments
TheTimeSavingCo
Forum|alt.badge.img+31

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") )

 

Adam, Airtable Consultant - thetimesaving.company
Terms & ConditionsAccessibility statement