Excel: Dates, Serial Date Value, and Leap Year

OK, now for something a little different. For you Excel users...

I was teaching an Excel Level 1 class at Keystone for Vail Resorts yesterday. As always in my Excel Level 1 class, I go over the concepts of how Excel deals with dates. So I thought this might be some good information for the blog and thus here it is.

As I explain in my Excel 1 class, if you type in a date in a cell of Excel such as 5/6/2010, you are really doing two things: assigning a value to the cell as well as a numerical format (Date). I continue to explain in the class that Excel uses what's called the 1900 calendar relationship which defines January 1, 1900 = 1.

Thus Excel really uses what is called the Date Serial Value or just Date Value in Date/Time calculations/functions. I also expain that Excel is aware of leap years...but then the plot thickened as just for grins I looked into this issue and low and behold here's the real story:

Pope Gregory XIII back in 1582 introduced a calendar to correct errors inherent in the prior Julian calendar.

The Gregorian calendar is based on a year of 365 days. But because the actual length of a year is 365.242190402 days, a "leap year" occurs every four years and consists of 366 days with February 29 being the extra day for the leap year. As you can see this is fine if the fraction were exactly .25 but it's .242190402. If you do the math (use Excel) you would discover that in a 400 year interval there should only be 97 extra days and not 100 as per above. To compensate for this error, the Gregorian calendar defines a "leap year" as: Any year that is evenly divisible by 4 but for years that are evenly divisible by 100, they must also be evenly divisible by 400 to be a "leap year" and consequently 3 of the 4 years in the 400 year peiod that would otherwise be a leap year aren't. Thus the years 1700, 1800, 1900, 2100, 2200, 2300, 2500, 2600 are not "leap years". Did you know that 1900 was not a leap year even though it is evenly divisible by 4? So what does this have to do with Excel you ask?? Ever heard of Lotus 1-2-3, Lotus Corp.?

Well, Lotus Corp. (makers of Lotus 1-2-3) assumed the year 1900 to be a leap year when it created the Lotus 1-2-3 program (to make things easier in date calculations, which works for the most part). Lotus 1-2-3 was a spreadsheet program that was around before Micosoft had Excel. Lotus's Date calculations were based on the 1900 calendar but incorrectly had 1900 as a leap year. To be compatible with Lotus, Microsoft decided to use the same date value relationships as Lotus and thus the 1900 calendar was adopted in Excel. SO, Jan 1, 1900 = 1 but also has a February 29 with a Date Value of 60. There shouldn't be a 2/29/1900 but THERE IS!...3/1/1900 should be 60 but it's 61...Microsoft documents this situation and notes that there are "workarounds" if you deal with dates prior to 3/1/1900. There is no problem with any calculations involving dates after 3/1/1900.

Holy smoke screens batman! Think about it..

Stay tuned...

And that's not's all, a "final" comment on shortcuts

What else might I know about shortcuts you ask...well "Page 4" as has been said before. Thanks Paul.

Have you ever noticed (particularly in Office applications version 2003 and earlier) there is an underlined letter in the Menu items eg File, Edit, etc.? Ever just wonder WHY?? Just because??

If you like using the keyboard, pressing the Alt key "activates" the keyboard menu commands, ie, the underlined letters. In other words, Alt>F (press Alt then press F) is the same as clicking "File" in the menu. Notice menus and submenus also have items with an underlined letter. Pressing the letter (if you have already pressed Alt) is like clicking on the item using your mouse. Thus in an earlier blog post I mentioned Ctrl+P (Ctrl and P keys pressed together), to print, could also be accomplished by Alt>F>P. Now you see that all Menu items have some keyboard shortcut!!

In Office 2007, pressing Alt causes a shortcut key to "highlight" over the Tabs, eg an H appears on the Home Tab after pressing Alt; pressing H causes the shortcut key or keys for the ribbon items to highlight eg an AR apprears on the align right button in Word 2007. Thus Alt>H>AR would align right a paragrah in Word 2007.

Thus, there are many options of getting things done without grabbing the mouse and having to click somewhere. It might behoove you to get to know a few of the keyboard shortcuts for things you tend to do repetitively! It's all about being efficient in gett'n er done! Do the best you can...

Stay tuned...