how to make February 29 appear!

[Archametis]

Hello all,
I am curently revamping some financial spreadsheets for work, and have an issue I would like help with. Traditionally, we just change the date in the first column of the sheet, and drag it down, this wouldn't be a problem, except in the last 10 years, we have grown to about 80 sheets in total, and its becoming TOO time consuming. I have set it up so I just have to change the first date and all others will follow monthly =A1+1, but when I get to feb, IF its not a leap year it shows 3/1. Is there anyway using conditional formatting, or something else that I can have it so it doesn't do this? Even a simple change font color to white would be helpful! I have tried conditional formatting, and used if greater than or equal to 3/1, but the issue is that the dates include the year right now, so dates are displayed as 2/1/2013, therefore 2/29/16 still wont show up. Any help would be grateful!

 

[barry houdini]

I'm not really clear what you want to do, Do you mean that you want to see 29th February in every year even if it's not a leap year.....or do you have trouble displaying 29th February in leap years like 2016?

 

[Archametis]

I'm not really clear what you want to do, Do you mean that you want to see 29th February in every year even if it's not a leap year.....or do you have trouble displaying 29th February in leap years like 2016?

I am having trouble displaying the 29 on leap years, and not displaying 3/1 on non leap years, as 3/1 would be on the march page. THANKS!

 

[Scott Huish]

It sounds like the op wants to leave a blank cell if February 29th doesn't exist but show it if it does.

 

[Scott Huish]

That bit about each month being on a separate page made it easier though, for your February page, change the formula to this. Assume starting date in A1.

=IF(MONTH(A1+1)=2,A1+1,"")

Copy down to A29.

 

[iyyi]

Hello all,
I am curently revamping some financial spreadsheets for work, and have an issue I would like help with. Traditionally, we just change the date in the first column of the sheet, and drag it down, this wouldn't be a problem, except in the last 10 years, we have grown to about 80 sheets in total, and its becoming TOO time consuming. I have set it up so I just have to change the first date and all others will follow monthly =A1+1, but when I get to feb, IF its not a leap year it shows 3/1. Is there anyway using conditional formatting, or something else that I can have it so it doesn't do this? Even a simple change font color to white would be helpful! I have tried conditional formatting, and used if greater than or equal to 3/1, but the issue is that the dates include the year right now, so dates are displayed as 2/1/2013, therefore 2/29/16 still wont show up. Any help would be grateful!

You write the first date of the month in A1
If you have excel 2007 or later you can use the formula in A2:
=IFERROR(IF(MONTH(A1+1)=MONTH(A1),A1+1,""),"") and drag to A31

If you have 2003 write in A2:
=IF(ISERROR(MONTH(A1+1)=MONTH(A1)),"",(IF(MONTH(A1+1)=MONTH(A1),A1+1,"")))
​and drag to A31

 

[Archametis]

THANK YOU! Hotpepper's solution worked perfectly. You have just saved me oodles of time, and frustrations! iyyi, thank you as well, this helps me understand a whole lot! again a thousand thanks!