CODEWITHSUNDEEP
google-sheets
Pylinux
Pylinux

Reputation: 11816

Get date from week number in Google Sheets

If I have week 7 in 2017 what week date is the Monday in that week in Google Sheets?

Upvotes: 32

Views: 59694

Answers (5)

Pylinux
Pylinux

Reputation: 11816

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 first day of the year

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:

This assumes Monday as the first day of week you have to change the arguments for WEEKDAY to change it to Sunday

Upvotes: 26

Drew Hart
Drew Hart

Reputation: 421

Worked this up for 2023. It will work through end of 2024 too .. that said the AND logic is flawed .. feel free to suggest something to make this better

=IFS(
AND(ISOWEEKNUM(A8)=52,YEAR(A8)<>YEAR(A7)),

DATE(YEAR(A8-1),1,1)-WEEKDAY(DATE(YEAR(A8-1),1,1),3)+7*(WEEKDAY(DATE(YEAR(A8-1),1,1),3)>3)+7*(ISOWEEKNUM(A8)-1),

DATE(YEAR(A8),1,1)-WEEKDAY(DATE(YEAR(A8),1,1),3)+7*(WEEKDAY(DATE(YEAR(A8),1,1),3)>3)+7*(ISOWEEKNUM(A8)-1)
)

Upvotes: 0

stallingOne
stallingOne

Reputation: 4006

If you are using ISO weeks, the accepted answer doesn't account for weeks overlapping on 2 technical years like 2020-w53, which is from 28 Dec 2020 until 3 Jan 2021.

Therefore I'm using this formula instead:

=DATE(K2,1,1)-WEEKDAY(DATE(K2,1,1),2)+7*(WEEKDAY(DATE(K2,1,1),2)>3)+7*(L2-1) +1

Where K is the Year, and L is the Week number (split in 2 columns from yyyy-ww)

to have it in an arrayformula:

=ArrayFormula(if(K2:K="",, DATE(K2:K,1,1)-WEEKDAY(DATE(K2:K,1,1),2)+7*(WEEKDAY(DATE(K2:K,1,1),2)>3)+7*(L2:L-1) +1 ))

You can use =ArrayFormula(if(E2:E="",,split(E2:E,"-"))) to split yyyy-ww in two columns.

NOTE: This formula would return the Monday (Which is the first day of the week in international standard, ISO)

Upvotes: 1

Buntstift
Buntstift

Reputation: 81

Because of this definition (https://en.wikipedia.org/wiki/Week) the 4th of January must be used instead the 1st. The 4th of January is the first day which is always in the week 1.

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

Upvotes: 8

Peter Svanberg
Peter Svanberg

Reputation: 356

=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 I know which works for week numbers in Sweden (i.e. Monday first day of week, ISO rules for what is week 1).

Upvotes: 34

Related Questions