Exact week number conversion to date

[Riccid]

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

[Kenda]

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.

[NicoleJohnson · Accepted solution]

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

[Riccid]

Superstar, Thanks!

[jdunkerley79]

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

[peddy]

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

[jdunkerley79]

@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

[peddy]

Nice one @jdunkerley79 🙂
Thanks!