Beyond my ability - help account for leap years

stepseazy

New Member
Joined
Apr 20, 2010
Messages
27
First off, sorry about the rudimentary code. Anyway, my code is not working because of leap years. How can I account for subtract out and then add back in leap years. The mathematical task is beyond my ability. THanks in advance. RIk

Code:
Sub back()
Dim begindate As Long
Dim enddate As Long
Dim datelength As Long
enddate = Cells(1, 2).Value
begindate = Cells(1, 1).Value
datelength = enddate - begindate
Cells(1, 2).Value = begindate
Cells(1, 1).Value = begindate - datelength
Macro2
End Sub
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
i cannot completely understand your question but generally leap year (2 digit code) should be divided by 4 without a remainder.
(there are a few exceptions to this rule for years xx00, but they a very rare)
year(now()) = 2010 so you try to divide 10 by 4
10 mod 4 = 2 (<>0 then it is not a leap one)
if you have for example 2012:
remove the gundreds to get 12 then divide by 4 : 12 mod 4 = 0 (Leap Year)
for any date:
if (year(some_date) mod 100) mod 4 = 0 then
msgbox "LeapYear"
Else
msgbox "Not a Leap Year"
End if
 

stepseazy

New Member
Joined
Apr 20, 2010
Messages
27
I understand that part of it, my question would be, how would you calculate the number of leap days between date x and date y.
 

stepseazy

New Member
Joined
Apr 20, 2010
Messages
27
Now that I think about it, I think the easiest way would be to subtract the number of days that would be between the days if all years were 365 from the actual number of days. I just gotta figure out how to do that.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
oops, not eactly true - thinking ....
:)
 

stepseazy

New Member
Joined
Apr 20, 2010
Messages
27
here we go-

number of days w/out leap years = dateserial(1995,month(enddate),day(enddate))-dateserial(1995,month(begindate),day(begindate))+365*(year(enddate)-year(begindate)

I'll have to check that out. (btw I just used 1995 randomly because it isnt a leap year
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
ROUND((YEAR(date1)-YEAR(date2))/4,0)
i beleieve this will give you the number of leap days between two dates
but you'll have to check the dates date1 and date2 - are they before or after the leap date if the year is leap and then eventually give or take a day

number of days w/out leap years = dateserial(1995,month(enddate),day(enddate))-dateserial(1995,month(begindate),day(begindate))+365*(year(enddate)-year(begindate)
this will probably give you some strange result unless begindate and enddate are the same days in the year (does it make sense) ??? :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,840
Messages
5,507,607
Members
408,641
Latest member
billigee

This Week's Hot Topics

Top