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 319 views

## 1 answer to this question.

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))`
answered Dec 25, 2022 by
• 63,700 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

## Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE