How to Convert Numbers to Dates in Excel

Messed up Dates or Numbers in Excel

Usually, two cases apply when you want to turn numbers to dates in Microsoft Excel:

  1. When the number is in a serial format. This usually happens when you copy dates and paste values or paste dates into cells with a number format.
  2. When the slashes or dashes in the dates are gone. For example, 10062005 instead of 10/06/2005. This occurs when you import data from .csv files or files from an external system.

In this article, you will learn the procedure on how to deal with these number-to-date troubles in Excel.

Convert an Excel Serial Number to Date in a Single Step

Here, you'll learn how to convert a serial number to a date by applying the date format.

  • Press CTRL + 1 to launch the format cells dialog;
  • Then, select the date category and assign the format Type. (Excel 2007 ribbons allow you to do it faster).

You can see below that the serial number 39,938 turns to the date very easily, as 05/05/2009. To paraphrase: the serial number 39,938 is Tuesday, 5 May 2009.

Why Are Serial Numbers Dates in Excel?

Excel manages dates as serial numbers.

What does this mean?

  • Imagine a line number, from 1 to 2,958,465, where 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.

Note: Avoid the following message: "negative dates or times are displayed as ############." Never apply the date format to a number outside the Excel serial number range.

Convert Numbers to Dates when the Year, Month, and Day are Together in a Cell

How do you 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 here: =DATE(RIGHT(A1,2);MID(A1,3,2);LEFT(A1,2))

Note: Use the functions according if and when the order changes. For example: year-month-day, month-year-day, and so forth.

More by this Author


Comments 1 comment

XP 4 years ago

very helpful! thanks!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working