CF to ignore leap year (2003 SP3)

[steveh]

Morning all

I have had a good Google about because I thought that this might be a common question but I can't really find any definitive answer.

I have a cell that is formatted as ddd-yyyy on 12 worksheets that represent months. On the February worksheet I would like to conditionally format the column that shows 29 to be hidden (by using white font, white background etc.) and to become visible if the condition is met, for simplicities sake something like =IF(C3=LeapYear)

Can this be done?

[HansV]

Can you tell us a bit more about the structure of the worksheet?

[steveh]

Can you tell us a bit more about the structure of the worksheet?

Hi Hans

I have a table that takes its values from a master calendar =Planner!I72 (the whole range is B7:AF67) - the headers are simply labels - Name, Department and in this case the numbers 1 - 29. (B6-AF6)

I would like to place a CF formula into AF7:AF67 that would hide any formatting etc. using CF if the date in C3 was a leap year, the cell is formatted as ddd-yyyy (Feb-2011). I found a MS KB article that seemed to suggest that if a particular cell held the date the folowing formula would show if the year was a leap year or not, that formula is

=IF(OR(MOD(C3,400)=0,AND(MOD(C3,4)=0,MOD(C3,100)<>0)),"Leap Year", "NOT a Leap Year")

I thought that by removing the Leap Year and Not a Leap Year part of the formula I could use it, ie. =IF(OR(MOD(C3,400)=0,AND(MOD(C3,4)=0,MOD(C3,100)<>0)) but I can't test this because it says that the formula is missing a parenthesis but I think I have tried to put one into every conceivable position without success

[HansV]

I assume that you mean mmm-yyyy as format instead of ddd-yyyy.

Use this formula:

=MONTH($C$3+28)=2

Since C3 contains the 1st of February, C3+28 is the 1st of March in ordinary years, but the 29th of February in leap years, so Month(C3+28) is 3 in ordinary years and 2 in leap years.

[steveh]

I assume that you mean mmm-yyyy as format instead of ddd-yyyy.

Use this formula:

=MONTH($C$3+28)=2

Since C3 contains the 1st of February, C3+28 is the 1st of March in ordinary years, but the 29th of February in leap years, so Month(C3+28) is 3 in ordinary years and 2 in leap years.

Great, Thank you very much Hans