We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
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