Dates And Times In Excel

Introduction
	Almost all projects and applications in Excel use dates and times in some way. A project may need to determine the number of days between two dates, or determine the date that is so many days in the future. Timesheet projects need to determine the elapsed time between and start and end times, or determine how many hours are overtime. Before you can take advantage of some of the more power date and time formulas, you have to understand how Excel stores dates and times. Regardless of how you have formatted a cell to display a date or time, Excel always internally stores dates And times the same way.
	How Excel Stores Dates And Times Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, orserial date-time. Dates The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days have passed since 1900-Jan-0. The number 1 represents 1900-Jan-1. It should be noted that the number 0 does not represent 1899-Dec-31. It does not. If you use the MONTH function with the date 0, it will return January, not December. Moreover, the YEAR function will return 1900, not 1899. Actually, this number is one greater than the actual number of days. This is because Excel behaves as if the date 1900-Feb-29 existed. It did not. The year 1900 was not a leap year (the year 2000 is a leap year). In Excel, the day after 1900-Feb-28 is 1900-Feb-29. In reality, the day after 1900-Feb-28 was 1900-Mar-1 . This is not a "bug". Indeed, it is by design. Excel works this way because it was truly a bug in Lotus 123. When Excel was introduced, 123 has nearly the entire market for spreadsheet software. Microsoft decided to continue Lotus' bug, in order to fully compatible. Users who switched from 123 to Excel would not have to make any changes to their data. As long as all your dates later than 1900-Mar-1, this should be of no concern. Times The fractional portion of the number, tttttt, represents the fractional portion of a 24 hour day. For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day. Similarly, 6PM is stored at 0.75, or 75% percent of a 24 hour day. As you can see, any date and time can be stored as the sum of the date and the time. For example, 3PM on 29-Jan-2000 is stored internally as 36544.625. When you enter a time without a value, such as entering 15:00 into a cell, the date portion is a zero. The zero indicates that there is no date associated with the time. You should remember that entering just a time does notautomatically put in the current date.

	The Two Date Systems Of Excel In the section above, it was said that the date portion of a serial date-time represents the number of days since 1900-Jan-0. This is true, but Excel can also treat the date portion of a serial date as the number of days since 1904-Jan-0. This mode is, called the 1904-mode or 1904-system, is used for compatibility with the Macintosh system. The date mode applies to all dates within a workbook. You cannot mix and match modes within a workbook. Unless absolutely required, you should never use the 1904-system. If you change systems after you've entered some dates in a workbook, these dates will appear to be off 4 years (because the serial dates haven't changed, only their display representation has). Moreover, linking workbooks with different date systems will cause problems.
	Entering Two Digit Years In Excel When you enter a date with only two digits in the year, you need to know how Excel treats the year. Excel uses 29/30 as the "cutoff point" between 1900 and 2000. For example, entering a year between 30 and 99 causes Excel to treat the year as 1930 - 1999. Entering a year between 00 and 29 causes Excel to treat the year as 2000 - 2029. Here are some examples: 1/1/00 = 2000-Jan-1 1/1/01 = 2001-Jan-1 1/1/29 = 2029-Jan-1 1/1/30 = 1930-Jan-1 1/1/31 = 1931-Jan-1 1/1/99 = 1999-Jan-1 The 29/30 cutoff is new to Excel97 and Excel2000. Excel95 uses 19/20 as the cutoff year. If you have any doubts about how Excel will treat 2-digit years, use the full 4-digit year. In Windows98 and Windows2000, you can specify the cutoff year, from the Regional Setting control in the Windows Control Panel. Note that the DATE worksheet function does not follow these rules. When you enter a number less than 1900 for the year parameter of the DATE function, DATE will add 1900 to that number. For example, =DATE(10,1,5) returns the date 5-Jan-1910, because DATE simply adds 10 to 1900 to compute the year. Similarly, the formula =DATE(150,1,5) returns the date 2050-Jan-5, because DATE adds 150 to the year 1900. DATE does not follow the same "cut off" rules that cells do.