Solved How to figure out if a given year is a leap year (1 Viewer)

[Ryan142]

Hi guys,

I think I have the answer to this but I don't know how to execute it in vba!

I need to figure out whether a given year is a leap year. My idea for doing this is taking the number (i.e. 2020) then dividing it by 4. If it is a whole number / Integer then it's a leap year, if it's a float value then it's not a leap year.

There's also an issue with the '00s of years, such as 1900, 2000, 2100. For them you have to divide by 400 and if it it's evenly divisible then it's a leap year and if not then it is a normal year. This is essentially the same problem of figuring out if a resultant number is an integer or a float value.

Any help si of cause appreciated, and any differing ideas would be fantastic to hear as well!

Thanks a lot,
Ryan

 

[plog]

IsLeapYear: IsDate("2/29/" & [YourYearField])

 

[Isaac]

Function IsLeapYear(lngYear As Long) As Boolean
Dim dtEOMFeb As Date, dtBOMMar As Date
dtBOMMar = DateSerial(lngYear, 3, 1)
dtEOMFeb = DateAdd("d", -1, dtBOMMar)
If DatePart("d", dtEOMFeb) = 29 Then
    IsLeapYear = True
Else
    IsLeapYear = False
End If
End Function

 

[Isaac]

EDIT .. Nice, much better, plog, saw yours after I clicked Post.
I actually tried a similar approach using CDATE(), and was disappointed to find that CDATE() did not throw any error when trying to convert a non-existent date (like 2/29/2019), and so gave up on that method and others like it...Too quickly, it seems, as IsDate() stays more true to fact.

 

[Pat Hartman]

When we had to do date math the hard way, we needed to know which years were leap years. Using Access, you have functions to do date math and they know which years are leap years so - why are you asking this question? Are you doing calculations manually that you should be using a date function to perform for you?

There is one rule for determining a leap year and one exception and one exception to the exception.
A year divisible by 4 is a leap year
- Except if the year is also divisible by 100
--- Unless the year is also divisible by 400

 

[The_Doc_Man]

And if you use Pat's rule you are good until (approximately) 3128, give or take. The Gregorian Calendar finally gets out of sync in that year.

The "IsDate" suggested by plog should work. Isaac's solution should work. However, if you are bound and determined to actually compute it, don't forget the MOD operation (modulus).

Here is Pat's rule, coded as an IF-ladder.

DIM iyear AS INTEGER    'or you could equally choose LONG
DIM LeapYear AS BOOLEAN
...
iyear = DATEPART( "yyyy", Now())
IF iyear MOD 4 <> 0 THEN
    LeapYear = FALSE
ELSEIF iyear MOD 100 <> 0 THEN
    LeapYear = TRUE
ELSEIF iyear MOD 400 = 0 THEN
    LeapYear = TRUE
ELSE
    LeapYear = FALSE
END IF

 

[Pat Hartman]

I'm sure when we get close to 3128 , we'll have to do the same kind of adjustment we did in the past to keep the seasons lined up with the months. Of course we didn't have computers last time. If any of you thought the Y2K debacle was a nightmare, don't sign up for the 3128 project

 

[deletedT]

The Gregorian Calendar finally gets out of sync in that year.

Why? Is there any article for studying about it? I had no luck with google.
Thanks.

 

[Galaxiom]

I'm sure when we get close to 3128 , we'll have to do the same kind of adjustment we did in the past to keep the seasons lined up with the months.

Let's hope everyone does it at once. Delays in introduction of the Gregorian Calendar resulted in a whole week being skipped in some countries. People protested about a week of their lives being stolen and some missed out on their birthdays.

 

[The_Doc_Man]

Actually, if I recall correctly, a LOT of disruption occurred - like, 10 days of it - in the switch from Julian to Gregorian calendars. But this all happened more than 200 years ago. It is why the longest night of the year (Winter Solstice) is now December 21st rather than January 1st. It is also why the USA President's day holiday is earlier than it really should be based on George Washington's birthday.

If anyone wants to do some computations, the correct number to use (to 7 digits) is that one sidereal year is 365.2422 calendar days. From there and only a very little math, you can derive the fractions that are involved.

4 x 365.2422 = 1460.9688, so every 4 years we get 96.88% of another full day because of the fraction.
100 x 365.2422 = 36524.22, so every 100 years, we get 24 extra days but NOT the 25th.
400 x 365.2422 = 146096.88, so every 400 years, we get an extra 88% of a day that should be a leap year.

That 88% means that we get 12% deficit every 400 years. Which means that about 8 x 400 = 3200 years since the adoption of the Gregorian calendar, we have a full day in error. So I guess that means that every 3200 years we will probably have an adjustment, except I've run out of decimal places to take the math any farther. However, it gets more complex than that because various orbital frictional forces make the length of the year non-uniform over that long a period. I suppose that means that some day, the International Astronomical Union will simply have to issue a press release saying we need to either skip or add a leap day in a year that normally wouldn't have one.

 

[Micron]

Fortunately for me, if they add an extra, extra day in 3128 AD and it is a work day, it won't affect me because I'm retired now.

 

[Uncle Gizmo]

IsLeapYear: IsDate("2/29/" & [YourYearField])

That's very clever!

Without knowing Plogs solution, I would have gone with Isaacs example, calculating the first day of the next month and moving back one, because I know that's the way you find the last day of a month.

 

[isladogs]

I agree. Plog's approach is so simple but I'd never thought of doing that.

The discussion about the calendars and 3128 being when a further adjustment will be needed reminded me about the late Chip Pearson's Easter calculator that I posted here http://www.mendipdatasystems.co.uk/easter-calculator/4594420644 (and at AWF). That is only guaranteed to be accurate until 2368. Does anyone have any idea why it may not be correct after that?

 

[The_Doc_Man]

Colin, I believe it is because the Easter holiday depends on LUNAR cycles that don't come out even either, but with different fractions. Easter is based on the Paschal full moon, which is the first full moon after March 21st. That means you have 29.5 days right there, but throwing in Leap Year considerations means a few extra days.

I would have to look it up to get exact numbers, but the problem there is that our moon's orbit is not phase-locked to our Earth orbit. The daily rotation of the moon IS phase-locked, so we only see one side of the moon from Earth. One lunar orbit of the Earth is 27.3 days but because of the Earth's own orbit making the position of the sun a moving target, the phase cycle is 29.5 days. That leads to a 2.2 day "deficit" which is why we often have 13 full moons in a given year. Since Easter is a LUNAR holiday based on a full moon phase, the fractions involved will eventually add up to move Easter to another day than any it has been using before. If I recall correctly, there are 35 different days that COULD be Easter Sunday.

Anyway, the direct answer as to why the calendar fails after 2368 is that the accumulating fractions involved in the computation eventually overwhelm it and it gets desync'ed from the calendar.

 

[Isaac]

That's very clever!

Without knowing Plogs solution, I would have gone with Isaacs example, calculating the first day of the next month and moving back one, because I know that's the way you find the last day of a month.

Yeah and it's funny, I was too lazy to even try IsDate once I found out CDate was worthless in evaluating whether "it's a date or not". Surprisingly. Apparently CDate is no use in that situation, but IsDate is smarter.

 

[isladogs]

Thanks Doc.
Sounds plausible to me though note my earlier comment that the Easter calculator is only guaranteed to be accurate until that year. Chip Pearson indicated that it may well still work after that.

Isaac
I'm baffled by your comment. Although IsDate is better, CDate can be used as well

 

[Isaac]

Isaac
I'm baffled by your comment. Although IsDate is better, CDate can be used as well

Well .... Now I'm baffled by my comment, too. I swear to God I tested it the other day several times and it didn't throw an error! Twilight zone moment!
At the moment I cannot remember precisely how I tested it, so I'm just guessing at that this must have been the situation:

This does throw an error (maybe this wasn't what I tested)

?cdate("2/29/2019")

This does not throw an error, and in fact, returns March 1st:

?cdate(dateserial(2019,2,29))

Figuring the best way to test this was using a method unimpacted by locales and date formats in the first place, I probably used DateSerial.

So I guess that's the result that I did not think was as helpful as it ought to be.

 

[jdraw]

For the curious:
? IsDate("2/29/" & "3132")
True
? IsDate("2/29/" & "3128")
True
? IsDate("2/29/" & "3200")
True

 

[deletedT]

That is only guaranteed to be accurate until 2368. Does anyone have any idea why it may not be correct after that?

@isladogs I'm a little bit confused. Am I doing something wrong or is it what you're talking about the guarantee?

Using your function mentioned above,
?GetEasterSunday(3132)
returns : 3132/04/22

then if I do :
?Weekday(3132/04/22 )

then I receive 7 that is Saturday.

But this calandar shows 3132/04/22 as Friday:

Year 3132 Calendar – United States

United States 3132 – Calendar with American holidays. Yearly calendar showing months for the year 3132. Calendars – online and print friendly – for any year and month

www.timeanddate.com

It maybe because I'm in Japan. or is it about the guarantee in the accuracy of the function?
We don't have Easter and I'm not sure about it? Isn't Easter on Sundays?

 

[isladogs]

Hi @Tera
Thank you for your response.
As 3132 is well beyond the year (2368) for which this is 'guaranteed' to work, I'm not surprised it is incorrect.
However, I didn't it to be the wrong day of the week.

Before publishing this code I tested it from 1900 to 2099 and it seemed to work perfectly against published data for the years I (and others) checked.
Pity I didn't test 2100 when the calculated day is a Saturday as it is until 2199.
From 2200 to 2299 its Friday, then its Thursday from 2300 to 2399 and again through to 2499.

Hopefully you can see the pattern here.
Although Chip Pearson's code is amazingly clever, it seems he forgot that century years such as 2100/2200/2300 are not leap years (see post #5)
I will look again at this function over the next few days and hopefully will be able to 'tweak' it with only minor changes

The code below has Chip's original function (converted from Excel to Access) and a test procedure I used.

Option Compare Database
Option Explicit

Public Function GetEasterSunday(Yr As Integer) As Date
 
'Code taken from http://www.cpearson.com/excel/Easter.aspx
 
   Dim D As Integer
   D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
   GetEasterSunday = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + D + (D > 48) + 1) Mod 7)
            
End Function

Sub CheckEaster()

Dim I As Integer
For I = 2000 To 2499
Debug.Print Format(GetEasterSunday(I), "ddd dd/mm/yyyy")
Next
End Sub