4 Week Months

[mar050703]

Hello

I am trying to work out a formula in excel that can narrow down a 4 week and say 2 day working month into 4 weeks. So if 30th and 31st would be in week 1 of the following month (along with 1,2, and 3rd) therefore Monday 6th would be Week 2.

I currently have the following formula:

CONCATENATE("Wk",INT((DAY(B2)+WEEKDAY(B2-DAY(B2)+2)+3)/7))

B2 being the cell with the date to calculate the week number.

Thanks

 

[zelgar]

Try this:

="WK"&IF(WEEKNUM(B2)-(MONTH(B2)-1)*4>4,WEEKNUM(B2)-MONTH(B2)*4,WEEKNUM(B2)-(MONTH(B2)-1)*4)

 

[SkipVought]

Hi,

=INT((SomeDate-2)/7)*7+2

I often used a formula like this to calculate a Monday from any Date during the week. The +/- constant can be adjusted to calculate a date other than Monday.

The INT() part calculates a week number based on Jan 1 1900.

So you can test when the year of that week changes to reset the week number for the year.

Similarly you can test the month of that week to reset the week number for the month.

I personally would not append alpha characters with these numbers. It makes them virtually useless. Rather I'd suggest a Number Format like "Wk"00.

 

[SkipVought]

So what happen when you have a 5-week month?