Add one year to Leap Year

[zinzah]

Hi all,

Been a while...

Thought i had it figured out, but discovered that Excel Worksheet formula and Excel VBA do not seem to calculate one year from a leap day the same.
In a spreadhseet, i have :

Which, in my mind, is correct and the value i want.
However, when using the following formula in a UserForm control:

the result is "02/28/2025"

I have tried a number of variations but cannot seem to get VBA to return 3/1/2025.

I need to stay away from adding 365/366 days or "add one year plus one day"...

Any thoughts on why they work differently? and a possible solution?

Chris

 

[awoohaw]

i can't tell you the specifics of how date math works in excel.
But, I've usually always subtracted the day of the month less one to get to the first of the month and then do all my math after that.
so to get March 1 of 2025 from 2/2/29 i would do something like:

Dim date1 As Date
Dim date2 As Date
date1 = DateSerial(2024, 2, 29)
date2 = DateAdd("m", 13, date1 - Day(date1) + 1)
MsgBox date1 & vbCrLf & date2

 

[Joe4]

Here is another way I got it to work:

Sub DateTest()

    Dim dte As Date
    Dim dte2 As Date
    
'   Set initial date
    dte = DateSerial(2024, 2, 29)
     
'   Build new date 12 months from first date
    dte2 = DateSerial(Year(dte), Month(dte) + 12, Day(dte))
    
'   Show value
    MsgBox Format(dte2, "MM/DD/YYYY")
    
End Sub

 

[awoohaw]

Here is another way I got it to work:

Sub DateTest()

    Dim dte As Date
    Dim dte2 As Date
   
'   Set initial date
    dte = DateSerial(2024, 2, 29)
    
'   Build new date 12 months from first date
    dte2 = DateSerial(Year(dte), Month(dte) + 12, Day(dte))
   
'   Show value
    MsgBox Format(dte2, "MM/DD/YYYY")
   
End Sub

Much cleaner and easier to understand @Joe4 ! A bit more typing and clarity is often worth it!

 

[zinzah]

@Joe4 @awoohaw

WooHoo!!!

Made some modifications to @Joe4 code for testing purposes...

Sub DateTest()

Dim dte As Date
Dim dte2 As Date
  
For i = 1 To 1000
'   Set initial date
    'dte = DateSerial(2024, 2, 29)
    dte = Format(DateSerial(Year(Worksheets("sheet1").Range("a" & i).Value), Month(Worksheets("sheet1").Range("a" & i).Value), Day(Worksheets("sheet1").Range("a" & i).Value)))
'   Build new date 12 months from first date
    dte2 = DateSerial(Year(dte), Month(dte) + 12, Day(dte))
    Worksheets("sheet1").Range("B" & i) = dte2
Next i

and the all important results:

Thank you both for the fast replies and awesome solutions!

 

[awoohaw]

That is great! I'm pleased you found a solution.

Best Wishes!

 

[bebo021999]

Or, you can use VBA to put formula into B1:B1000, then get value, like this:

With Range("B1:B1000")
    .Formula = "=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))"
    .Value = .Value
End With

or, looping from B1:B1000:
use one of the 4 lines inside for...loop

For i = 1 To 10000
    'Range("B" & i).Formula = "=DATE(YEAR(A" & i & " )+1,MONTH(A" & i & "),DAY(A" & i & "))"
 'or
    'Range("B" & i).Formula = "=DATE(YEAR(A" & i & " ),MONTH(A" & i & ")+12,DAY(A" & i & "))"
'or
    'Range("B" & i).Value = Evaluate("=DATE(YEAR(A" & i & " )+1,MONTH(A" & i & "),DAY(A" & i & "))")
'or
    'Range("B" & i).Value = Evaluate("=DATE(YEAR(A" & i & " ),MONTH(A" & i & ")+12,DAY(A" & i & "))")
Next

 

[zinzah]

@

@bebo021999​

Haha! Thanks for the alternatives...but the loop was strictly for testing. Needed to make sure that 2/28/2023 went out 365 days and then from March 1, 2023 thru February 29, 2024 was 366 , especially with February 29, 2024 going out until March 1, 2025.

 

[Joe4]

Glad we were able to help!