Exact week number conversion to date
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi there Community!
Here is one task I am finding a bit challenging:
I have a series of data that provide the year and week number in the format: YYYYWW .
I am trying to convert it into a date, specifically each week's Friday.
I have already tried the below formula, however the conversion does not seem correct.
DateTimeAdd(left([YYYYWW], 4)+"-01-01", (tonumber(right([YYYYWW], 2))-1)*7 , "days")
Looking forward to hear from you, I am looking for a solution that considers the year's start day so that it is reusable in future.
Thanks,
Denise
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Riccid! I think it is because you're trying to use the DateTimeAdd function, but it doesn't recognize that your formula created a date. Not sure, but that's what it seems. Open the attached workflow that I created, and see if that is what you're looking for.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can actually calculate the Friday date in a formula tool (all in one, if you don't mind a bit of a hairy looking formula)...
DateTimeAdd(DateTimeAdd(DateTimeParse(Left(tostring([Field1]),4),"%Y"),5-tonumber(DateTimeFormat(DateTimeParse(Left(tostring([Field1]),4),"%Y"),"%w")),"days"),(ToNumber(Right(tostring([Field1]),2))-1)*7,"days")
Essentially, it figures out what day of the week the first day of the year is, then converts it to Friday, then adds x number of weeks (week number * 7) to get to the Friday date for each week. If you wanted to break into individual formulas, you could do so as follows:
Week: ToNumber(Right(ToString([Field1]),2))
WeekdayFirstOfYear: DateTimeFormat(DateTimeParse(Left(ToString([Field1]),4),"%Y"),"%w")
StartDate: DateTimeAdd(DateTimeParse(Left(tostring([Field1]),4),"%Y"),5-tonumber([WeekdayFirstOfYear]),"days")
FridayDate: DateTimeAdd([StartDate],([Week]-1)*7,"days")
Hope that helps give you another option! :)
Cheers,
NJ
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Superstar, Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I did it essentially the same way as @NicoleJohnson but prefer a stage calculation in this case:
- Create 1st January for the Date:
Left([Field1],4)+"-01-01"
-Move to First Friday:
DateTimeAdd([Date],Mod(5-ToNumber(DateTimeFormat([Date],"%w")), 7), "days")
- Parse week, subtract 1 (as starting on first Friday) multiply by 7 and add days
DateTimeAdd([Date], (ToNumber(Right([Field1],2)) - 1) * 7, "days")
Sample attached
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This solution doesn't work for year's that have 53 weeks in them.
both solutions above produced the following output:
202052 | 2020-12-25 00:00:00 |
202053 | 2021-01-01 00:00:00 |
202101 | 2021-01-01 00:00:00 |
However, 202101 should be "2021-01-08".
I've been trying to figure it out, but struggling. Any ideas?
Pedram
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@peddy - you are correct we missed a key detail.
The ISO-8601 definition is the week that contains the first Thursday of the year should be the start.
So breaking it down:
- First, create 1st January:
ToString(floor([YYYYWW]/100))+"-01-01"
- If it is after Thursday move forward a week:
DateTimeAdd([YearStart],
iif(DateTimeFormat([YearStart],"%w")>"4",7,0),
"days")
- Now move to Monday:
DateTimeAdd([YearStart],
1 - ToNumber(DateTimeFormat([YearStart],"%w")),
"days")
After that it's much more straight forward:
DateTimeAdd([YearStart],MOD([YYYYWW],100)*7 - 7,"days")
This produces the Monday for the specified week number. If you want Friday:
DateTimeAdd([YearStart],MOD([YYYYWW],100)*7 - 7+4,"days")
Sample attached
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Nice one @jdunkerley79 🙂
Thanks!
