XL: Method to Determine Whether a Year Is a Leap Year |
The information in this article applies to:
-
Microsoft Excel for Windows, versions 2.1, 3.0, 4.0, 4.0a, 5.0, 5.0c
-
Microsoft Excel for OS/2, versions 2.2, 3.0
-
Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0
-
Microsoft Excel for Windows NT, version 5.0
-
Microsoft Excel for Windows 95, versions 7.0, 7.0a
-
Microsoft Excel 97 for Windows
-
Microsoft Excel 98 Macintosh Edition
SUMMARY
This article contains information that explains how to determine whether
the year in a date used in a Microsoft Excel document is a leap year.
MORE INFORMATION
The date system used by Microsoft Excel is based on the Gregorian
calendar, first established in 1582 by Pope Gregory XIII. The Gregorian
calendar was designed to correct the errors introduced by the less
accurate Julian calendar.
In the Gregorian calendar, a normal year consists of 365 days. Because the
actual length of a sidereal year (the time required for the Earth to
revolve once about the Sun) is actually 365.25635 days, a "leap year" of
366 days is used once every four years to eliminate the error caused by
three normal (but short) years. Any year that is evenly divisible by 4 is
a leap year: for example, 1988, 1992, and 1996 are leap years.
However, there is still a small error that must be accounted for. To
eliminate this error, the Gregorian calendar stipulates that a year that
is evenly divisible by 100 (for example, 1900) is a leap year only if
it is also evenly divisible by 400.
For this reason, the following years ARE NOT leap years
1700, 1800, 1900, 2100, 2200, 2300, 2500, 2600
because they are evenly divisible by 100 but NOT by 400.
The following years ARE leap years
1600, 2000, 2400
because they are evenly divisible by both 100 and 400.
Because earlier versions of Microsoft Excel handle only years from 1900 to
2078, only the year 1900 is subject to the 100/400 exclusion rule of leap
years in Microsoft Excel. However, in order to be compatible with other
programs, Microsoft Excel treats the year 1900 as a leap year.
NOTE: Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition handle
years from 1900 to 9999. Although Microsoft Excel 97 and Microsoft Excel
98
Macintosh Edition still treat the year 1900 as a leap year, all other
non-leap years (2100, 2200, and so on) are handled correctly.
For additional information, please see the following article in the
Microsoft Knowledge Base:
Q106339 : XL: Days of the Week Before March 1, 1900, Are Incorrect
To Determine If a Year Is a Leap Year
To determine if a year is a leap year, follow these steps:
- If the year is evenly divisible by 4, go to step 2. Otherwise, go to
step 5.
- If the year is evenly divisible by 100, go to step 3. Otherwise, go to
step 4.
- If the year is evenly divisible by 400, go to step 4. Otherwise, go to
step 5.
- The year is a leap year (it has 366 days).
- The year is not a leap year (it has 365 days).
Formula to Determine If a Year Is a Leap Year
The following formula will determine whether the year number entered into
a cell (in this example, cell A1) is a leap year:
=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"Leap Year",
"NOT a Leap Year")
If the value in cell A1 is this The formula returns
-----------------------------------------------------
1992 Leap Year
2000 Leap Year
1900 NOT a Leap Year
REFERENCES
For more information about how Microsoft products are affected by year
2000 (Y2K) issues, please see the following Microsoft World Wide Web site:
http://www.microsoft.com/technet/year2k/default.htm
Additional query words:
2.00 2.20 2.21 4.00a 5.00c 7.00a 97 8.0 98 XL98 XL97 XL7 XL5 XL4 y2k year2000 year 2000
Keywords : kb2000 xlformula
Version : MACINTOSH:2.2,3.0,4.0,5.0,98; WINDOWS:2.1,3.0,4.0,4.0a,5.0,5.0c,7.0,7.0a,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type :
|
