Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Re: Help with DATETIME_DIFF Formula

169 1
Showing results for 
Search instead for 
Did you mean: 

Hi team,

I’m hoping for some help. I am trying to work out the number of weeks between 2 dates. It keeps returning the results Nan and I have no idea what I am doing wrong. Here is the formula I am using.

I want to know how many weeks between “Cancel Date” and “Sale Date”. If anyone could give some input or advise it would be massively appreciated. Thanks

{Cancel Date},
{Sale Date (from Sales)},

7 Replies 7

Hey Peta, hmm that looks right. Could you provide a screenshot of the relevant tables / fields so that I could troubleshoot it perhaps?

In the meantime, I’ve set something up here for you that you can use to compare against your own base

Screenshot 2022-07-19 at 12.54.27 PM
Screenshot 2022-07-19 at 12.54.25 PM

Thanks so much for your help Adam. Here are 2 screen shots
Screen Shot 2022-07-19 at 2.24.39 pm
Screen Shot 2022-07-19 at 2.24.29 pm

Hey Peta, it looks like the Cancel Date field is a Single Line Text field; could you try changing it into a Date field?

OMG you are my hero!!! It works!!! thank you so much!!! I really really appreciate your help. Thank you!

@Adam_TheTimeSavingCo Hi Adam, could you also check my formula? I’m having the same problem as @Peta_Howlett but both fields, in my case, are already in Date format.

Screen Shot 2022-07-20 at 17.05.24
Screen Shot 2022-07-20 at 17.05.33

Hi Anita, your problem’s probably to do with the formatting of the Duration field I reckon

Screenshot 2022-07-20 at 6.23.40 PM

Try setting it to Integer instead of Duration

Thanks so much for your help. It worked!