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
Solved! Go to Solution.
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
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)?
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.
Perfect! Thanks jdunkerley79
User | Count |
---|---|
60 | |
24 | |
24 | |
21 | |
21 |