Date (mm/dd/yyyy) from Week number
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?
Best Answer
-
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.
- Adding 27 * 7 (189) to 01/01/2021
Answers
-
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.
- Adding 27 * 7 (189) to 01/01/2021
-
Thank you. This was really helpful!
-
@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
-
@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!
-
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)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!