Date (mm/dd/yyyy) from Week number

Stephen Meier

I am trying to get the date displayed from a week number. For instance this week is the 27th week of the year. I am trying to get it to display 07/05/2021 (the first day in the week).

I was thinking of converting the week to day of the year (i.e. 27 * 7 = 189) and using the 189 to convert to a date, but I can't figure out how. Does someone know how I would be able to do this?

Toufong Vang

Try this...

=IF(WEEKDAY(DATE(Year@row,1,1) + Week@row*7) = 1, DATE(Year@row,1,1) + Week@row*7, (DATE(Year@row,1,1) + Week@row*7) - (WEEKDAY(DATE(Year@row,1,1) + Week@row*7) - 1))

= IF(WEEKDAY(DATE(Year@row,1,1) + Week@row*7) = 1
  ,DATE(Year@row,1,1) + Week@row*7
  ,(DATE(Year@row,1,1) + Week@row*7) - (WEEKDAY(DATE(Year@row,1,1) + Week@row*7) - 1) )

The start of the week is Sunday where WEEKDAY() of some date equals 1.

Change "1" to "2" if you want Monday.

Why this works...

• Adding 27 * 7 (189) to 01/01/2021 DATE(Year@row,1,1) + Week@row*7 gets you to 7/9/2021.
• WEEKDAY() of 7/9/2021 = 6, which is 5 away from 1.
• Subtracting 5 days from 7/9/2021 to gets you to 7/4/2021 of which WEEKDAY() is 1.

Toufong Vang

Try this...

=IF(WEEKDAY(DATE(Year@row,1,1) + Week@row*7) = 1, DATE(Year@row,1,1) + Week@row*7, (DATE(Year@row,1,1) + Week@row*7) - (WEEKDAY(DATE(Year@row,1,1) + Week@row*7) - 1))

= IF(WEEKDAY(DATE(Year@row,1,1) + Week@row*7) = 1
  ,DATE(Year@row,1,1) + Week@row*7
  ,(DATE(Year@row,1,1) + Week@row*7) - (WEEKDAY(DATE(Year@row,1,1) + Week@row*7) - 1) )

The start of the week is Sunday where WEEKDAY() of some date equals 1.

Change "1" to "2" if you want Monday.

Why this works...

• Adding 27 * 7 (189) to 01/01/2021 DATE(Year@row,1,1) + Week@row*7 gets you to 7/9/2021.
• WEEKDAY() of 7/9/2021 = 6, which is 5 away from 1.
• Subtracting 5 days from 7/9/2021 to gets you to 7/4/2021 of which WEEKDAY() is 1.

Stephen Meier

Thank you. This was really helpful!

Shile

@Toufong Vang Hello, would there be any columns required for this to work. I made a Year and week column and reference them with your formula, but I get Invalid Column Valid error. I would appreciate your help. Thanks

CNaenfeldt

@Toufong Vang I've tried this but I don't get it to work.

My week is 50, and the start date of that week should be 12/11/2023, however when I use the formula above it gives me 12/18/2023.

=IF(WEEKDAY(DATE(Year@row, 1, 1) + Week@row * 7) = 2, DATE(Year@row, 1, 1) + Week@row * 7, (DATE(Year@row, 1, 1) + Week@row * 7) - (WEEKDAY(DATE(Year@row, 1, 1) + Week@row * 7) - 2))

Any suggestion on what I'm doing wrong?

Thank you!

Toufong Vang

Give these a try.

SUNDAY

=IF(WEEKDAY(DATE(Year@row, 1, 1)) = 1, ((Week@row - 1) * 7) + DATE(Year@row, 1, 1), ((Week@row - 1) * 7) + DATE(Year@row, 1, 1) + ((8 - WEEKDAY(DATE(Year@row, 1, 1)) + DATE(Year@row, 1, 1)) - DATE(Year@row, 1, 1)))

MONDAY

=IF(WEEKDAY(DATE(Year@row, 1, 1)) = 1, ((Week@row - 1) * 7) + DATE(Year@row, 1, 1) + 1, IF(WEEKDAY(DATE(Year@row, 1, 1)) = 2, (Week@row * 7) + DATE(Year@row, 1, 1), ((Week@row - 1) * 7) + DATE(Year@row, 1, 1) + ((8 - WEEKDAY(DATE(Year@row, 1, 1)) + DATE(Year@row, 1, 1)) - DATE(Year@row, 1, 1) + 1)))