# Beyond my ability - help account for leap years

#### stepseazy

##### New Member
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### bobsan42

##### Well-known Member
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
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
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

oops, not eactly true - thinking ....

#### stepseazy

##### New Member
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
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) ???

Replies
7
Views
274
Replies
1
Views
163
Replies
0
Views
215
Replies
4
Views
352
Replies
7
Views
639

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,713
Messages
5,833,269
Members
430,200
Latest member

### 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.

### Which adblocker are you using?

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

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