What are Excel Serial Numbers?
Have you ever tried to generate a date or time value in Excel and ended up seeing something that looked like a number instead? These numeric values are called serial numbers, and Excel stores date and time information in this format in order to perform calculations on these values. That is, it may be obvious to you and me that the “difference" between January 5 and January 6 of the same year is 1 day, but Excel needs to convert these dates to real numbers like 39452 and 39453 before it can subtract one value from the other.
How does this conversion process work? For dates, the Windows version of Excel uses a method known as the “1900 system" in which January 1, 1900 is assigned the serial number of 1, and each date after that is assigned a new serial number in sequence. For example, January 2, 1900 corresponds to the serial number 2 and January 10, 1900 corresponds to 10. Following this same line of reasoning, the date September 18, 2009 would correspond to the serial number 40074 since it is that many days after January 1, 1900. (Note: The Macintosh version of Excel uses the “1904" system which assigns January 1, 1904 to the serial number 1 instead.)
Excel also has a standard method of converting time values to serial numbers, and it does so in such a manner that a time serial number can be combined with a date serial number so that a value that designates both date and time can be represented by one number.
In this instance, the time 12:00:00 AM (midnight) corresponds to the decimal serial number 0 and the time 11:59:59 PM (59 minutes and 59 seconds past 11 PM) corresponds to the decimal serial number 0.99999999. Any time between midnight and 11:59:59 PM has a serial number that is sequentially placed between these two values. For instance, 12:00:00 PM (noon) corresponds to the serial number 0.5 since it is “half-way" through the day.
As mentioned above, a date and time can be combined into one serial number in order to perform calculations. For example, February 10, 2009, 12:00 PM has the serial number 39854.5 where the 39854 portion of the value corresponds to the date of February 10, 2009 and the 0.5 portion corresponds to the time of 12:00 PM.