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

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
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

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,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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) ??? :)
 
Master Excel Bundle

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.

Forum statistics

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

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
Top