CODEWITHSUNDEEP
excelexcel-formulaweek-number
Soru Soravic
Soru Soravic

Reputation: 97

Calculate the week number of a given date using a given a reference year

I have a formula which calculates the week number of a given date:

=WEEKNUM(Sheet2!N83)

I want a formula which returns the week number using 2018 as reference, in a similar manner as the above formula does. If the year is 2019, I want it to return weeknum + 52, so for 2019-01-01 I get week number '53'.

How can I achieve this?

Upvotes: 2

Views: 88

Answers (2)

sɐunıɔןɐqɐp
sɐunıɔןɐqɐp

Reputation: 3512

You actually need a formula which calculates the difference in weeks.

In A1 cell, fill the first day of the reference year (e.g. 2018-01-01).

In A2 cell, put the day of the week of your interest (e.g. 2019-01-03).

In the A3 cell, use this formula to calculate the difference in weeks (in case your week begins at Mondays):

=ROUNDDOWN((DATEDIF(B1-WEEKDAY(B1-2), B2, "d") / 7), 0) + 1

Or this one (in case your week begins at Sundays):

=ROUNDDOWN((DATEDIF(B1-WEEKDAY(B1-1), B2, "d") / 7), 0) + 1

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

This is only valid for years 2018 and 2019:

=IF(YEAR(Sheet2!N83)=2018,WEEKNUM(Sheet2!N83),WEEKNUM(Sheet2!N83)+52)

or:

=IF(YEAR(Sheet2!N83)=2018,WEEKNUM(Sheet2!N83),WEEKNUM(DATE(2018,MONTH(Sheet2!N83),DAY(Sheet2!N83)))+52)

Upvotes: 1

Related Questions