DateTimeFormat Specifers

[BarleyCorn]

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

[jdunkerley79]

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]

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 · Accepted solution]

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]

Perfect! Thanks jdunkerley79