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!

Find more posts tagged with

Formulas

Comments

Kara Lumley

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

Kara Lumley

UPDATE: There is an error in my formula. I'm working on it and will post a new link shortly.

Kara Lumley

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

ker9

Hi Kara,

Thank you for this. I will try it out over the weekend. Appreciate the help!

Karin

CanadaJim

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

Crystal Panning

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

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…