Messed up Dates or Numbers in Excel
Usually, two cases apply when you want to turn numbers to dates in Microsoft Excel:
- 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.
- 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.
This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.
Bimal shrestha on July 03, 2018:
how to make a change date in excel?
XP on May 31, 2012:
very helpful! thanks!