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    |
----------------------
```

The 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?

Dec 25, 2022 in Others 256 views

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))`
• 63,420 points

Calculate the number of days between a cell and today in excel?

Use the DATEDIF function when you want ...READ MORE

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

Excel - Make a graph that shows number of occurrences of each value in a column

There is probably a better way to ...READ MORE

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

Date difference: different results in Excel vs. Python

To calculate the date difference, we can ...READ MORE

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE