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