Geek Answers Handbook

Get date from week number in Google Sheets

If I have the 7th week of 2017, then what date of the week is Monday of this week on Google Sheets?

+12
source share
3 answers
=DATE(B9,1,1)-WEEKDAY(DATE(B9,1,1),3)+7*(WEEKDAY(DATE(B9,1,1),3)>3)+7*(A9-1)

is the least complicated formula that I know that works for week numbers in Sweden (i.e. Monday first day of week , ISO rules for week 1 ).

+18
source

Short answer (A1 == Week, B1 == Year):

 =DATE(B1;1;1)+((A1-1)*7)-WEEKDAY(DATE(B1;1;1);3)

Long answer:

 DATE(<year>;1;1) // days since 1970 until the frist day of the year

a plus

 ((<week number>-1)*7) // how many days into the year is this week

minus

 WEEKDAY(DATE(<year>;1;1);3) // how many extra days from previous year in first week

PS:

It is assumed that on Monday as the first day of the week you need to change the arguments for WEEKDAY to change it to Sunday

+13
source

Due to this definition ( https://en.wikipedia.org/wiki/Week ) January 4 should be used instead of the 1st. January 4 is the first day of week 1.

 =DATE(B1;1;4)+((A1-1)*7)-WEEKDAY(DATE(B1;1;4);3)
+1
source

More articles:


All Articles