31

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
Pylinux
  • 11,278
  • 4
  • 60
  • 67

5 Answers5

34
=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).

Pradeep
  • 9,667
  • 13
  • 27
  • 34
Peter Svanberg
  • 356
  • 3
  • 5
23

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

Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
Pylinux
  • 11,278
  • 4
  • 60
  • 67
7

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)
Buntstift
  • 71
  • 1
  • 2
1

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)

stallingOne
  • 3,633
  • 3
  • 41
  • 63
0

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)
)
Drew Hart
  • 421
  • 3
  • 3