Formula needed if it is even possible

[CORY]

Ok, it's hard to explain but I'll try my best. Firstly I'm using Exce
2002. Well here goes nothing. My problem consists of just one cell i
column B. Starting with cell B55 the whole column is dates, ex:
B55 (any date)[5-May]
B56 =if(B55>0,B55+1,"0")[6-May]
B57 =if(B55>0,B55+2,"0")[7-May]
B58 =if(B55>0,B55+3,"0")[8-May]
B59 =if(B55>0,B55+4,"0")[9-May]
B60 =if(B55>0,B55+5,"0")[10-May]
B61 =if(B55>0,B55+6,"0")[11-May]
B62
B63
B64 =if(B55>0,B55+7,"0")[12-May]

Baically continueing down the rows (skippins over cells B62, B63, B71
B72, B73, B81, B82, B83, B91, B92, B93, for seperations of the weeks).
I'm using the "if" formula for the date so that if there is no date i
B55 then the rest of the rows result in "0" which I have as
conditional format (for the whole column) to be the color of white (i
other words it doesn't show up). Now my problem occurs when I reac
the 5th week of the month ex:
B94 =if(B55>0,B55+29,"0")[3-Jun]

I want B94 to result in a "0" if the monthes are not the same, sinc
some monthes actually have 5 weeks I need the five weeks of formulas.
If cell B55 was 1-Jul then cell B94 would be 30-Jul, in which case
would need the 5th week. But most monthes don't have 5 weeks so i wan
tha cell B94 to result in "0" in those cases.
I hope that wasn't to confusing, I wrote it and I'm a little confused.
If anyone can help me with a formula can achive the desired effect tha
I want, I would be very appreciative.
Cor

 

[Michael Hopwood]

=if(B55>0,if(month(B55+29)<>month(b55),"0",b55+29),"0")

=if(B55>0,if(month(B55+29)<>month(b55),"",b55+29),"")

If you are returning zeros (as text) from these formulas and then formatting
them as white, why not use the second formula above and do away with the
conditional formatting. The second formula returns an empty string ("").

 

[Kieran]

You could try something like
B56 =if(B55>0,if(month(B55+1)=month(b55), b55+ 1, "0"),"0")

or alternatively

B56 =if(and(B55>0, month(B55+1)=month(b55)), b55+ 1, "0")

Basically either formula checks that the month of the derived date i
the same as the month of B55 and results in "0' if not.

Copy down the formula as appropriate

 

[CORY]

Thanks Kieran, worked exactly how I needed it to.

Cor