Excel question again.

aliasojo

Got another question.

Is it possible to get Excel to recognise when it's a 4 week month or a 5 week month?

I'm going to have two rows on my sheet. If it's a 4 week month I'll bold the 4 week figures or highlight in some other way and fade the 5 weeks figures. If it's a 5 week I'll highlight the 5 week figures.

Is there a way of highlighting automatically using conditional formatting maybe?

I've no idea if it's even possible to take dates/weeks into account on a spreadsheet.

gb12345

There's no built in function to return the number of weeks in a month, but it should be possible using the WEEKNUM function.

Try this

=IF(MONTH(NOW())<>12,WEEKNUM(DATE(YEAR(NOW()),MONTH(NOW())+1,1)),53)-WEEKNUM(DATE(YEAR(NOW()),MONTH(NOW()),1))

Put it in a cell somewhere then do conditional formatting based on the value of that cell being 4 or 5 (don't know what version of Excel you are using this works in 2013, but I can't remember if you can do this in earlier versions).

aliasojo

gb12345 wrote: »

There's no built in function to return the number of weeks in a month, but it should be possible using the WEEKNUM function.

Try this

=IF(MONTH(NOW())<>12,WEEKNUM(DATE(YEAR(NOW()),MONTH(NOW())+1,1)),53)-WEEKNUM(DATE(YEAR(NOW()),MONTH(NOW()),1))

Put it in a cell somewhere then do conditional formatting based on the value of that cell being 4 or 5 (don't know what version of Excel you are using this works in 2013, but I can't remember if you can do this in earlier versions).

I'm using 2010.

Thanks..will try that shortly.