Setting a Custom week number in Excel

**raahl** · 06-06-2012, 11:19 AM

Hi I need to set work weeks starting on the first Monday after Feb 27th 2012. So the week commencing 27th would be seen as WW 1. The formula below was posted by Daddylonlegs here:
http://www.excelforum.com/excel-gene...html?p=2814016

Here's the code:

Please Login or Register  to view this content.

Cell x3 contains the current date, which the formula will then calculate the week number for.
In this case, the first week was the First Monday on or after 28th June, with the relevant fiscal year being July 1st onwards ? Sorry, but I'm not very good with excel date logic. Would anyone be able to describe to me what the key parts of the formula are doing? I can understand that the first part is establishing what day of the week the date in X3 is, but after that, I'm stumped. I thought that the X3+188 might relate to the number of days after Jan 1st, the fiscal date (July 1st in the example) might be, in conjunction with the 6,20 being 20th June, but when I change these values, the formula breaks. Any help would be appreciated. Any help would be appreciated.

Thanks

**daddylonglegs** · 06-06-2012, 01:43 PM

Hello raahl, thanks for re-posting as requested

The 188 in that formula is the number of days until the end of the year - it's a little difficult to make it generic for any date, because if the start of the fiscal year is before end of February the number of days to the end of the year isn't fixed (it changes in a leap year), and if the start of year is after the end of February the alternative - days back to 1st January - is also variable. Your start date is particularly challenging because "1st Monday on or after 27th February" could be before or after the end of February and consequently before or after any leap day!

Having said that this version should work for you and is more easily altered for any date

=INT((A2-LOOKUP(A2,DATE(YEAR(A2)-{2,1,0},2,27)-WEEKDAY(DATE(YEAR(A2)-{2,1,0},2,27-2))+7))/7)+1

The only parts that might need to change are those in blue and red

The red parts signify the earliest start date, 27(th) of February (2) and the blue 2 signifies the start day (1 = Sunday through to 7 = Saturday)

**raahl** · 06-07-2012, 03:04 AM

Originally Posted by daddylonglegs

Having said that this version should work for you and is more easily altered for any date

=INT((A2-LOOKUP(A2,DATE(YEAR(A2)-{2,1,0},2,27)-WEEKDAY(DATE(YEAR(A2)-{2,1,0},2,27-2))+7))/7)+1

The only parts that might need to change are those in blue and red

The red parts signify the earliest start date, 27(th) of February (2) and the blue 2 signifies the start day (1 = Sunday through to 7 = Saturday)

Genius, thanks Daddylonglegs, much appreciated.

R