Help with DATETIME_DIFF Formula

[Peta_Howlett]

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

DATETIME_DIFF(
{Cancel Date},
{Sale Date (from Sales)},
‘weeks’
)

[TheTimeSavingCo]

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

[Peta_Howlett]

Thanks so much for your help Adam. Here are 2 screen shots

[TheTimeSavingCo]

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

[Peta_Howlett]

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

[Anita_Pangestan]

@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.

[TheTimeSavingCo]

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

Try setting it to Integer instead of Duration

[Anita_Pangestan]

Thanks so much for your help. It worked!