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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
oops, not eactly true - thinking ....
:)
 
Upvote 0
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
 
Upvote 0
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) ??? :)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top