CODEWITHSUNDEEP
exceldatedatetimeexcel-formulaformula
R0bert
R0bert

Reputation: 557

Calculating the week number of a year in Excel

I'm trying to calculate the week number of a week in a year in excel using the formula =WEEKNUM(A1,2).

My Result:

----------------------
|  Date    |   Week  |
|2021-01-01|    1    |
|2021-01-02|    1    |
|2021-01-03|    1    |
|2021-01-04|    1    |
|2021-01-05|    1    |
|2021-01-06|    1    |
|2021-01-07|    1    |
----------------------

Result which I want:

----------------------
|  Date    |   Week  |
|2021-01-01|    53   |
|2021-01-02|    53   |
|2021-01-03|    53   |
|2021-01-04|    1    |
|2021-01-05|    1    |
|2021-01-06|    1    |
|2021-01-07|    1    |
----------------------

Since Date 1,2,3 are in the last week of the last year.

How do I get the above result

Upvotes: 0

Views: 1500

Answers (1)

R0bert
R0bert

Reputation: 557

Based on @ScottCraner comment, I was able to get the result. Here is my excel formula.

=IF(AND(A1>=DATE(YEAR(A1),1,1),A1<DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,6))),53,WEEKNUM(A1,2))

Upvotes: 1

Related Questions