# Formula to show week by month, reset each month, and year, reset each year

#### PJVV77

##### New Member
Hi,

I have a date set that starts on the 1st of January 1990 and runs through to 31st of December 2030. I need to show for each date a week number of the month and I found a formula (shown below) that can reset the week number for each month (my weeks start on a Monday). However, when my year changes the month resetting does not work properly; I want it to start from week 1 again once the year changes and when the portion of January in 1991, that still forms part of the last week of December 1990, is completed. I attached an image to demonstrate this. I cannot work out how to incorporate this in the formula. See below example and any help would be most appreciated.

The formula I use is =WEEKNUM(DATE(YEAR(A1),1,DAY(A1+1-WEEKDAY(A1,2))),2) [I added in +1 as for some reason Excel states that the 1st of Janaury 1990 is a Sunday as opposed to a Monday?]

Thank you, Pieter

#### Attachments

• Week by Month.PNG
11.3 KB · Views: 10

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

like this?

#### Attachments

• Week by Month v2.PNG
13.4 KB · Views: 6

hope like this

#### PJVV77

##### New Member

hope like this
View attachment 24593

Yes! exactly like that. I have updated my Excel and OS details too, thanks!

#### sandy666

##### Banned - Rules violations
you can use Power Query (Get&Transform)
- create new column Start of Week
- create next column Week of Month
- subtract 1 from Week of Month
- remove unnecessary column(s)

btw. weeks will start from 0 not from 1
all your dates = 14975 rows
1st of January 1990 and runs through to 31st of December 2030

#### PJVV77

##### New Member
you can use Power Query (Get&Transform)
- create new column Start of Week
- create next column Week of Month
- subtract 1 from Week of Month
- remove unnecessary column(s)

btw. weeks will start from 0 not from 1
all your dates = 14975 rows
Thank you Sandy, but I think you are a bit more skilled in PowerQuery than I am...
Could you give me the step by step details of the formulas etc. as I cannot replicate your calculations (sorry!).

thanks!

#### sandy666

##### Banned - Rules violations
Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Date = Table.TransformColumnTypes(Source,{{"Date", type date}}),
SoW = Table.AddColumn(Date, "Start of Week", each Date.StartOfWeek([Date]), type date),
WoM = Table.AddColumn(SoW, "Week of Month", each Date.WeekOfMonth([Start of Week]), Int64.Type),
Subtract = Table.TransformColumns(WoM, {{"Week of Month", each _ - 1, type number}}),
RC = Table.RemoveColumns(Subtract,{"Start of Week"})
in
RC``````
where Table1 is a source of the dates

 12/01/1990 12/01/1990 1 13/01/1990 13/01/1990 1 14/01/1990 14/01/1990 1 15/01/1990 15/01/1990 2 16/01/1990 16/01/1990 2 17/01/1990 17/01/1990 2 18/01/1990 18/01/1990 2 19/01/1990 19/01/1990 2 20/01/1990 20/01/1990 2 21/01/1990 21/01/1990 2 22/01/1990 22/01/1990 3 23/01/1990 23/01/1990 3 24/01/1990 24/01/1990 3

Replies
4
Views
126
Replies
1
Views
125
Replies
3
Views
65
Replies
38
Views
1K
Replies
3
Views
99

1,127,650
Messages
5,626,084
Members
416,161
Latest member
David1966Lewis

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back