Calculating the week number of a year in Excel

0 votes

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 by Kithuzzz
• 38,000 points
511 views

1 answer to this question.

0 votes

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 narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

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

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

answered Nov 8, 2022 in Others by gaurav
• 23,260 points
716 views
0 votes
1 answer

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

answered Sep 30, 2022 in Others by narikkadan
• 63,600 points
904 views
0 votes
1 answer

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

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

answered Oct 21, 2022 in Others by narikkadan
• 63,600 points
9,454 views
0 votes
1 answer

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

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
797 views
0 votes
1 answer

Date difference: different results in Excel vs. Python

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

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,150 views
0 votes
1 answer

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

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

answered Sep 27, 2022 in Others by narikkadan
• 63,600 points
1,056 views
0 votes
1 answer

Excel formula to check date within this week and last week

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

answered Sep 29, 2022 in Others by narikkadan
• 63,600 points
1,987 views
0 votes
1 answer

Excel COUNTIF formula

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

answered Sep 29, 2022 in Others by narikkadan
• 63,600 points
811 views
0 votes
1 answer

Excel Formula calculating the starting date of a given calendar week

The number of days to take into ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,600 points
700 views
0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,600 points
618 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP