DateTimeDiff in Weeks

[nyanyanunu]

Hi,

I keep getting null value from DateTimeDiff function.

I want to calculate the week difference between product launch week and today.

Below are my formula and data:

Please advise.

Thanks,

Claire

[MarqueeCrew · Accepted solution]

@nyanyanunu,

The reason for that is that the unit of "weeks" is not supported.  You would have to use "days" and then divide by 7.  This won't get you whole numbers.  You'll need to use something like Ceiling() to get it to round up.

Cheers,

Mark

[pcatterson · Accepted solution]

Like MarqueeCrew stated, if you want it tp round up always:

Ceil(DateTimeDiff([Today],[Date],'days')/7)

or if you want it to round down always:

Floor(DateTimeDiff([Today],[Date],'days')/7)

or if you want it to round based on the number of days:

Round(DateTimeDiff([Today],[Date],'days')/7,1)

[nyanyanunu]

Thank you so much Mark! I'm able to get the correct numbers now 

[nyanyanunu]

Thank you so much for your deailed explination! Celi finction fits my situation

[Scot401]

Years later and this is helpful but TODAY isn't working for me. I have only been using Alteryx a few days so maybe its something obvious. I tried:

Round(DateTimeDiff([Today],[Date],'days')/7,1) it is erroring as "Unknown variable "Today"

[Scot401]

I found the answer so posting it here for other rookies!

Today has to be somewhere in the workflow so I had to add a separate formula for Today which was DateTimeToday() and then my calculation formula Round(DateTimeDiff([Today],[Date],'days')/7,1)

[Jhar2401]

I have used the formula suggested to convert days to weeks (round(....)) and I still get a "null" result...any ideas?

[Jhar2401]

ignore that...although in the formula tool it says "null" it does seem to have worked when i have run it again