+ Reply to Thread
Results 1 to 10 of 10

DateAdd Function affected by leap year

  1. #1
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 16
    Posts
    3

    DateAdd Function affected by leap year

    I have created Userform to choose a start date and the end date will be after 365 days. I am using DateAdd function however I face a problem with leap year. For example, if I choose the starting date to be 28 Feb 2020, the end date will be 27 Feb 2020. But because of leap year if I choose 1 March 2020 as start date the end date will be 1 March 2021. May I know how to get the date to be 28 Feb 2021?
    Last edited by yona22; 03-01-2020 at 05:30 AM.

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: DateAdd Function affected by leap year

    Use 'EDATE' function and add 12 months.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: DateAdd Function affected by leap year

    Further to Torachan's suggestion, see here: https://docs.microsoft.com/en-us/off...function.edate
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: DateAdd Function affected by leap year

    Quote Originally Posted by torachan View Post
    Use 'EDATE' function and add 12 months.
    I do not think that does what the OP wants. He appears to want the date one day less than a full year from the start date... with the exception of Feb 29th of a leap year, WorksheetFunction.EDate returns the same day one year hence. And subtracting 1 does not work because it screws up when the start date is Feb 29th of a leap year.



    Quote Originally Posted by yona22 View Post
    But because of leap year if I choose 1 March 2020 as start date the end date will be 1 March 2021. May I know how to get the date to be 28 Feb 2021?
    Assuming your start date is stored in a variable named StartDate, I think this will do what you want...

    EndDate = DateAdd("yyyy", 1, StartDate) + (Format(StartDate, "mmdd") <> "0229")
    Last edited by Rick Rothstein; 03-01-2020 at 06:14 AM.

  5. #5
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 16
    Posts
    3
    Hi Rick,

    EndDate = DateAdd("yyyy", 1, StartDate) + (Format(StartDate, "mmdd") <> "0229")

    This code works fine but is the start date is 29 Feb 2024 the end date will be 27 Feb 2025 but it should be 28 Feb 2025. Can you help to solve this problem?

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: DateAdd Function affected by leap year

    It does not do that for me. If StartDate is February 29, 2024 the code line returns February 28, 2025 when I execute it. If you are not see that happen for you, can you post a copy of the workbook (see yellow banner at the top of the forum's webpage) where my code line fails so I can see exactly what you see on the sheet?

  7. #7
    Registered User
    Join Date
    03-01-2020
    Location
    London
    MS-Off Ver
    Office 16
    Posts
    3

    Re: DateAdd Function affected by leap year

    Hi Rick,

    I tried again and it works perfectly! Thank you so much.

  8. #8
    Registered User
    Join Date
    07-02-2018
    Location
    USA, CA, San Jose
    MS-Off Ver
    2010
    Posts
    2

    Re: DateAdd Function affected by leap year

    Quote Originally Posted by Rick Rothstein View Post
    It does not do that for me. If StartDate is February 29, 2024 the code line returns February 28, 2025 when I execute it. If you are not see that happen for you, can you post a copy of the workbook (see yellow banner at the top of the forum's webpage) where my code line fails so I can see exactly what you see on the sheet?
    having the exact same problem as you. wondering if you found a solution or someone here can help with this??? why is it happening? thanks

  9. #9
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: DateAdd Function affected by leap year

    Quote Originally Posted by Swithim View Post
    having the exact same problem as you. wondering if you found a solution or someone here can help with this??? why is it happening? thanks
    Given that the OP said in Message #7 that the code I posted in Message #4 ended up working perfectly for him., can you tell us which "exact same problem" you are having?

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: DateAdd Function affected by leap year

    Administrative Note:
    Swithim,
    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto populate calendar with Leap Year and Non-Leap year.
    By sickreto in forum Excel General
    Replies: 11
    Last Post: 12-05-2019, 01:46 PM
  2. [SOLVED] DateAdd from December to January not updating year
    By armlegx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2016, 07:06 PM
  3. How to Know Leap Year?
    By IMA_Saihat in forum Tips and Tutorials
    Replies: 11
    Last Post: 04-05-2015, 03:17 PM
  4. How to check if a year in a cell is a leap year
    By Azuko in forum Excel General
    Replies: 2
    Last Post: 01-31-2014, 07:08 AM
  5. How to check if a year in a cell is a leap year
    By Azuko in forum Excel General
    Replies: 1
    Last Post: 01-27-2014, 03:20 AM
  6. [SOLVED] Date function not accounting for leap year
    By SpencerD1985 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-19-2013, 05:48 AM
  7. Having a problem with my If Leap Year Function
    By Cruse in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-28-2013, 04:12 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1