Results 1 to 10 of 10

Thread: Month of February and leap year

  1. #1
    Acolyte rdwray's Avatar
    Join Date
    Mar 2017
    Posts
    33
    Articles
    0
    Excel Version
    2010

    Month of February and leap year



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,706
    Articles
    0
    Excel Version
    O365
    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.

  3. #3
    Acolyte rdwray's Avatar
    Join Date
    Mar 2017
    Posts
    33
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Bob Phillips View Post
    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.
    Right, but there is never more than 29 days in February.

  4. #4
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    43
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    What's the bug?

    Date(2019,3,0) => 2/28/19
    Date(2020,3,0) => 2/29/20

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,706
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by rdwray View Post
    Right, but there is never more than 29 days in February.
    How does that answer of my question, and what is the relevance of that to my statement?

  6. #6
    Acolyte rdwray's Avatar
    Join Date
    Mar 2017
    Posts
    33
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Bob Phillips View Post
    How does that answer of my question, and what is the relevance of that to my statement?
    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)

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,706
    Articles
    0
    Excel Version
    O365
    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.

  8. #8
    Acolyte rdwray's Avatar
    Join Date
    Mar 2017
    Posts
    33
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Bob Phillips View Post
    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.
    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.
    If you can't read the formula, then I can't see that you can be of any help.

  9. #9
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    43
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Quote Originally Posted by rdwray View Post
    If you can't read the formula, then I can't see that you can be of any help.
    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?

  10. #10
    Acolyte rdwray's Avatar
    Join Date
    Mar 2017
    Posts
    33
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NormS View Post
    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?
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •