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

NicoleJohnson

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