Date from Week Number

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

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

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