Date from Week Number
Is it possible to get a date (mm/dd/yy) from a week number? Ultimately I'd like to get the Friday of the week number.
Using today as an example (1/11/18), the week number is 2 and Friday is 1/12/18. I've tried some different Excel formulas but am not getting anything to work in Smartsheet.
Thanks!
Comments
-
Hi Ker9,
This is a bit complex. I have made a sheet that you can save off a copy of for yourself (save as new) with the formulas you need. Plug in dates in row 13 in the "Date" column.
Clicking this link will save a copy of the sheet, called Day of Week formulas under your Sheets tab.
(the link will work for anyone else as well, if they are interested in seeing the formulas).
Hope this helps,
Kara
-
UPDATE: There is an error in my formula. I'm working on it and will post a new link shortly.
-
Please use this link for the corrected formula. My previous sheet did not take into account if the Friday was in a different month than other days of the week
Best,
Kara
-
Hi Kara,
Thank you for this. I will try it out over the weekend. Appreciate the help!
Karin
-
If you're trying to find the date of a Friday of a given week when starting with a particular date, then this will work:
=IF(WEEKDAY([DateColumn]1) = 6, [DateColumn]1, IF(WEEKDAY([DateColumn]1) = 7, [DateColumn]1 - 1, [DateColumn]1 + 6 - WEEKDAY([DateColumn]1)))
It basically says if it's already a Friday, you're good. Then if it's a Saturday, use the day before. Finally, if it's before Friday, say Thursday, you add 6 to the original date then subtract the day of the week (5), so in the end you're adding 1 day to make it the Friday.
But if you're starting with a week number, you can assume that either Jan 1 is in week 1 or Jan 7 is in week one. Then do something like this to find a date in the week:
For Jan 1: =DATE(2018, 1, 1) + 7 * ([WeekNumber]1 - 1)
For Jan 7: =DATE(2018, 1, 7) + 7 * ([WeekNumber]1 - 1)
It basically adds 7 days for each ADDITIONAL week of the year from the start of the year, so you get a date in the original week.
This will get you a day in the week number you're interested in, then you can apply the top formula to find the Friday.
That makes it a bit simpler than the above answer, but might need some tweaks to work just the way you want it.
Hope it helps!
Jim
-
Hi,
In regards to WeekNumber formula, I have a formula that is taking a date and applying the week number to it. For example 1/27/19 will apply week # 4 to it being the 4th week of the year. Unfortunately it only calcs off the Sunday & Monday date, so when I switch the date to a Friday 2/1/19 (should still be week # 4), it moves it to the next week. I need to alter the formula to ensure the Sunday-Friday range registers as the same week #. Are you able to help find a forumla for this? My current formula is =WEEKNUMBER(Date@row)
Thank you,
Crystal
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!