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.
Converting Dates and Times to Serial Numbers
There are a number of Excel functions that deal with date and time information, but the two that are most commonly used to convert this information to serial numbers are DATEVALUE and TIMEVALUE.
The DATEVALUE function uses the following syntax.
In this case, the argument date_text refers to the text that is used to denote a specific date, such as Mar 12, 1980 or 02/19/2009. The screenshot below gives an example of this usage. Note that, in the image, the Excel Serial Number column has been formatted to show values with 10 decimal places. This formatting option was chosen so that the entire time value would be displayed. (Click any image for a larger view.)
The TIMEVALUE function is similar in construction to the DATEVALUE function and has the following basic syntax.
Here, time_text denotes the text used to specify an exact time, such as 1:39 PM or 15:30. See below for an example.
Converting Serial Numbers to Date and Time Values
It’s more common for users to want to know how to convert serial numbers back to some recognizable date and time format. There are several ways to accomplish this, but the easiest way is to simply change the format of the cell.
For instance, suppose we have the serial number 40002 and we want to display that date in the form “Month Day, Year”. Begin by right-clicking on the cell containing this value and choosing Format Cells.
When the Format Cells dialog box appears on the screen, click on the Number tab and then choose Date from the list of categories. Scroll through the Type list and find the format you wish to apply.
Click OK to continue and the format will be applied to the cell.
A similar process can be used to convert serial numbers into time values and into mixed date and time values. There are additional methods that can be used to make these conversions, and we will be exploring those in other articles.
If you’re looking for more tips and tricks, be sure to browse through the other Microsoft Excel user guides and tutorials available here on Bright Hub’s Windows Channel. Learn about Excel charts and graphs, the differences between various lookup and reference functions, and more. Additional tips are being added on a regular basis, so check back often.