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

2
votes

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?

2
excelexcel-formulaweek-number

2 Answers

1
votes

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)
1
votes

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