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

[PJVV77]

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

 

[sandy666]

like this?

 

[PJVV77]

like this?
View attachment 24591

Hi, no like this (see Should Read column)

 

[sandy666]

hope like this

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

 

[PJVV77]

hope like this
View attachment 24593

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

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

 

[sandy666]

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]

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]

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