How to Convert Serial Numbers to Dates in Excel

Have you ever tried typing a date into a blank cell of an Excel file and it pops back with a random number? Have you ever wondered if that numeric value means anything? Surprisingly, it does. This is how Excel makes sense of dates. But I want to see the date not some random number, can I change it to show short and long dates? Absolutely! Excel has a conversion process that can easily convert serial numbers (or serial values) to dates in Excel, but first, you need to understand these random numbers.

What are Excel's Serial Numbers?

Excel uses serial numbers to represent dates because it's a practical way to calculate dates. This is the way Excel stores dates. When you enter a date into Excel, it converts it into an internal numeric format. This format simplifies date-related calculations like addition, subtraction, and comparison.

In Microsoft Excel:

• Each day is assigned a unique number, starting from January 1, 1900, which is represented as the number 1. This is the serial number of the date.
• The day after that (January 2, 1900) is assigned the number 2, and so on. This means that dates in Excel are basically sequential serial numbers.

This numeric representation allows Excel to perform date calculations by simply adding or subtracting numbers, making it more efficient for the software to process. While it might seem cryptic to users, it's a logical and efficient way for Excel to work with dates behind the scenes.

How to Convert Serial Numbers to Dates in Excel

Here's a simple step-by-step guide on how to convert serial numbers to dates in Microsoft Excel:

Select the Cells with Serial Dates

First, open your Excel sheet and select the cells containing the serial dates that you want to convert.

Open the ‘Format Cells' Dialogue Box

• Navigate to the “Home” tab in the Excel ribbon.
• Locate the “Number” group.
• Click on the small drop-down button in the lower-right corner of this group. It looks like an exclamation mark inside a yellow diamond.

Specify the Date Format

You'll see several tabs once you've opened the “Format Cells” dialogue box. Click on the “Number” tab if it's not already selected. Here, you can choose from various number formats.

Choose the Desired Date Format

In the “Category” list on the left, select “Date.” Now, in the “Type” list on the right, you'll see different date formats you can apply. Choose the one that suits your needs, such as “Short Date” or “Custom.”

Apply the Format

Click the OK button to apply the selected date format to the Excel serial date numbers in your worksheet. Voila! Your selected cell (or selected cells if you're formatting more than one cell) is now displayed as actual dates.

How to Convert Dates to Serial Numbers in Excel

Pay Attention to Cell Formatting

When it comes to dates, you need to pay particular attention to how the cell is formatted. For example, if a date is formatted as text, it will be left aligned (just like general text is in a cell). If the date is formatted as a date, it will be right aligned. This is really important when you're trying to use formulas or functions to manipulate date data.

The DATEVALUE Function

The DATEVALUE function will take dates formatted as text and convert them to serial numbers. Using our example above, we can work backwards to figure out the serial number for December 12, 2012. The formula is as follows

=DATEVALUE(date_text)

In the above example, the DATEVALUE function returns the value of 41263, which is the numerical date in Excel for December 20, 2012.

The VALUE Function

You can also use the VALUE function for this as well. The VALUE accomplishes other tasks, but for the purpose of this blog post, just know that it allows for different formats; you can use it to convert dates formatted as date OR text to serial numbers. The formula is:

=VALUE(date_text)

Note the distinction: dates CAN BE formatted as date or text to use the VALUE function, and dates MUST be formatted as text to use the DATEVALUE function.

I feel that the VALUE function is the better option as it doesn't matter if your data is formatted as text format or date format, it will still calculate the serial number value.

WORKDAY and NETWORKDAYS Function

If you're trying to Subtract Dates in Excel to Get Days, Months, or Years or Subtract a Date from Today in Excel, you may be using the WORKDAY or NETWORKDAYS function. As part of these two functions, you can remove holidays from your calculation using either date in date format or dates in number format (serial number). Let's use NETWORKDAYS for the following example:

=NETWORKDAYS(start_date, end_date, [holidays])

The number of workdays (not the total number of days) between the two dates is 83. When determining holidays, it didn't matter if we used the date formatted cells (in column A) or the number formatted cells (in column B), both returned the same answer.

Frequently Asked Questions (FAQs)

Can I Convert Text Strings to Dates in Excel?

Yes, you can convert text strings that represent dates into actual dates in Excel. You can use the DATEVALUE function for this purpose. Simply enter the text string in an empty cell and use the following formula =DATEVALUE(C1), where “C1” is the cell reference containing the text dates in Column C. Excel will convert it into a date value (serial number).

How Do I Handle Leap Years When Converting Serial Dates?

Excel automatically accounts for leap years when converting serial dates to regular dates. You don't need to worry about adjusting the date values; Excel takes care of it for you. In the example below you can see that 2024 is a leap year, yet the serial numbers are in sequential order because Excel recognizes that February 29, 2024 exists.

That's It!

With the newfound knowledge of converting an Excel serial number to a date format, you now possess a valuable skill for managing date-related data in your spreadsheets. Using the “Format Cells” dialogue box and Excel's efficient functions like DATEVALUE and VALUE, you can effortlessly transform serial numbers into familiar date formats. So, go ahead, unmask those serial dates, and work with your data more effectively.