Month of February and leap year

R

rdwray

New member
Joined
Mar 25, 2017
Messages
38
Reaction score
0
Points
0
Excel Version(s)
2010
The formula below works for all months outside of leap year and every month except February during leap year which returns two additional days of 30 and 31.
Code: 
IF(O24+1<DATE(YEAR(B2),MONTH(B2)+1,0),O24+1,IF(O24+1=DATE(YEAR(B2),MONTH(B2)+1,0),DATE(YEAR(B2),MONTH(B2)+1,0),1))

The date (2/1/2020) is in cell B2.
O24 (Saturday) contains 29.
C29 (Sunday) contains 30.
E29 (Monday) contains 31.
G29 (Tuesday) contains 1.

The internet seems to explain this as resulting from Lotus 1-2-3 because it had a bug and they wanted to maintain compatibility in Excel. How do I get around this bug?
 
What exactly is the formula supposed to do? 29 will always be less than any date, aside from the first 30 days in Jan 1900.
 
Bob Phillips said:
What exactly is the formula supposed to do? 29 will always be less than any date, aside from the first 30 days in Jan 1900.
Click to expand...

Right, but there is never more than 29 days in February.
 
What's the bug?

Date(2019,3,0) => 2/28/19
Date(2020,3,0) => 2/29/20
 
rdwray said:
Right, but there is never more than 29 days in February.
Click to expand...

How does that answer of my question, and what is the relevance of that to my statement?
 
Bob Phillips said:
How does that answer of my question, and what is the relevance of that to my statement?
Click to expand...

Your statement had no relevance to my question, you used your own code. See what result you get with EOMONTH.
Code: 
Custom format a cell "d" and enter:
=EOMONTH(2020,2)
 
So you won't answer my question, and you address my statement by raising a functgion you didn't use and adds nothing to our understanding of your need. You do not deserve help, and you will get none from me.​
 
Bob Phillips said:
So you won't answer my question, and you address my statement by raising a functgion you didn't use and adds nothing to our understanding of your need. You do not deserve help, and you will get none from me.​
Click to expand...

What exactly is the formula supposed to do? 29 will always be less than any date, aside from the first 30 days in Jan 1900.
Click to expand...

If you can't read the formula, then I can't see that you can be of any help.
 
rdwray said:
If you can't read the formula, then I can't see that you can be of any help.
Click to expand...

You missed an opportunity, there's probably only a handful of people who know as much about Excel as Bob Phillips does.

In the future, attach a workbook when you post, it makes it so much easier for others to help you and will prevent these misunderstandings. Some questions I had,

What cell is that formula entered in?
Does O24 contain the value 29, or does it contain a date and is formatted to display 29?
What do C29, E29 and G29 have to do with this?
 
NormS said:
You missed an opportunity, there's probably only a handful of people who know as much about Excel as Bob Phillips does.

In the future, attach a workbook when you post, it makes it so much easier for others to help you and will prevent these misunderstandings. Some questions I had,

What cell is that formula entered in?
Does O24 contain the value 29, or does it contain a date and is formatted to display 29?
What do C29, E29 and G29 have to do with this?
Click to expand...

I appreciate any assistance, but Mr. Phillips did not seem interested in what I needed and wanted to interject his way of doing things when I was working with 28 different cells in each of 12 months of calendars.

Thanks for the comeback, but I have since resolved the issue. The problem was that Excel will return text rather than a real value and also the bug in February returning days 30 and 31 because of the text issue.
 
You must log in or register to reply here.
Back
Top