Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

DateTimeDiff in Weeks

nyanyanunu
6 - Meteoroid

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:

1.png

2.png

 

Please advise.

 

Thanks,

Claire

8 REPLIES 8
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
pcatterson
11 - Bolide

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
6 - Meteoroid

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

nyanyanunu
6 - Meteoroid

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

Scot401
5 - Atom

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
5 - Atom

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
6 - Meteoroid

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

Jhar2401
6 - Meteoroid

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

Labels