How to Convert an Excel Number to Date

You usually find two cases:

1.    When the Number Is in Serial Format; this happens when you copy Dates and paste values or paste dates into cells with Number format.

2.    When the slashes or dashes are gone: 10062005 instead of 10/06/2005. This occurs when you import data from csv files or files from an external system.

Here you will receive detailed procedures to deal with Excel Number to Date troubles:

Let's initiate with the first case...

Convert a Serial Number to a Date by applying it Date Format.

Press CTRL + 1 to launch the Format Cells dialog; then, select Date category and assign the Format Type. (Excel 2007 ribbons allow you to do it faster).

The serial number 39,938 turns to date very easily 05/05/2009. To paraphrase; the serial number 39,938 is Tuesday, 5 May 2009.

Here’s why?

Excel manages dates as serial numbers.

What does this mean? Imagine a line number from 1 to 2,958,465: integer 1 is January 01 1900 and integer 2,958,465 is December 31 9999. This serial number system (1 to 2,958,465) is referred as Excel Date-Time Code.

Avoid the following message: "negative dates or times are displayed as ############" by never applying date format to a number outside the Excel Serial number range.

Turn 150204 into 15/02/2004?

Use the Function DATE(year, month, day)

Extract each part from 160208 with these string functions: RIGHT, LEFT and MID as shown below:

=DATE(RIGHT(A1,2);MID(A1,3,2);LEFT(A1,2))

Use the functions wisely when the order changes: year-month-day, month-year-day, etc.