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

PJVV77

New Member
Joined
Aug 2, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
    Week by Month.PNG
    11.3 KB · Views: 10

Some videos you may like

Excel Facts

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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
hope like this
week.png


btw. update your profile (Account details) about Excel version and OS
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you can use Power Query (Get&Transform)
- load your date column to the Power Query Editor
- 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
Joined
Aug 2, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
you can use Power Query (Get&Transform)
- load your date column to the Power Query Editor
- 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
Joined
Oct 24, 2015
Messages
7,499
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/199012/01/19901
13/01/199013/01/19901
14/01/199014/01/19901
15/01/199015/01/19902
16/01/199016/01/19902
17/01/199017/01/19902
18/01/199018/01/19902
19/01/199019/01/19902
20/01/199020/01/19902
21/01/199021/01/19902
22/01/199022/01/19903
23/01/199023/01/19903
24/01/199024/01/19903
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top