Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

Alteryx Designer Desktop Discussions

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

DateTimeFormat Specifers

BarleyCorn
8 - Asteroid

The DateTimeFormat specifier: %W Returns the week number, as 00 - 53, with the beginning of weeks as Monday. Is there a way of converting this back to a series of dates as all W/C 06/04/2016. That is, 00 - 53 in date format and all week commencing Monday?

 

Thanks

 

4 REPLIES 4
jdunkerley79
ACE Emeritus
ACE Emeritus

Yes you can do this using a formula tool.

 

First find the Monday for the 0th week of the year. Assuming you have a year column:

datetimeadd(tostring(year,0)+'-01-01',-tonumber(datetimeformat(tostring(year,0)+'-01-01',"%d")),"days")

you can then use DateTimeAdd to add multiples of 7 days to get week commencing date:

datetimeadd(week0comm,ToNumber(week)*7,"days")

Attached workflow shows this

BarleyCorn
8 - Asteroid

Thanks jdunkerley - got it working, but the the week commencing is two days off? 31/12/2014 (wed) when start date is 29/12/2014 (Mon)?

jdunkerley79
ACE Emeritus
ACE Emeritus

Sorry typo (or incompetence) in the first formula:

datetimeadd(tostring(year,0)+'-01-01',1 - tonumber(datetimeformat(tostring(year,0)+'-01-01',"%w")),"days")

%w instead of %d to shift it backwards. The 1 moves it from Sunday to Monday.

BarleyCorn
8 - Asteroid

Perfect! Thanks jdunkerley79

Labels
Top Solution Authors