Convert an Excel Number to Date When the Number Is in Serial Format or as Stripped Date

Here you will receive specific procedures to tackle Excel Number to Date conversion problems:

  • Receiving a infinite ########### message because you format a Number that is not a Date (outside the Excel Serial Number range)
  • Getting lost when you format a Date back to Number General Format
  • Receiving a CSV file from a different system with stripped dates (160208 which would be 16-02-2008)

Let’s start…

Convert an Excel Number to Date in A Single Step When It Represents a Serial Number

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

In detail; use the Format Cells dialog, press CTRL + 1 then select Date category and assign the Format Type (see graphic 1 and graphic 3).

Graphic 1

Convert Number to Date, CTRL + 1

Excel 2007 users can use the ribbons (see graphic 2).

Graphic 2

Short Date Ribbon

After applying Date format to 39,938 it became 05/05/2009. In other words; the serial number 39,938 is Tuesday, 5 May 2009.

Graphic 3

Number Converted to a Date

Why so simple?

A number is a date in Excel; the software uses a very simple approach to manage dates: imagine a line number from 1 to 2,958,465: number 1 is January 01 1900 and number 2,958,465 is December 31 9999.

This serial number system (1 to 2,958,465) is called the Excel Date-Time Code. How are you benefited from this? You can add, subtract dates as you do with numbers.

And that’s not all…

There is a justified confusion about Excel Dates, why? Excel Formula Bar does not behave as expected.

Try this: convert 39,938 to a date (graphic 4) you shall see 39,938.00 at the Formula Bar but you see 05/05/2009 as the cell content. Wait, it was supposed that Formula Bar shows you behind-scenes-processes…

Graphic 4

Excel Date at the Formula Bar

Tip 1 Use = to see corresponding Serial Number at the Formula Bar

Graphic 5

Excel Date Seen as Number in the Formula Bar

Tip 2 Never apply date format to a number outside the Excel Serial number range.

Why? It doesn’t make sense and the message “negative dates or times are displayed as ############” will appear. This is also called a Negative Date.

Graphic 5

Tip 3 Convert a date to number, copy date and paste values, you can use DATE function too.

Convert an Excel Number to Date when the Year, Month and Day are Joined in a Single Number

How to turn 160208 into 16-02-2008? Stripped Dates come in two ways:

1) When you have some irregularities in the year, month or day

  • 160208 instead of 16022008
  • 16208 instead of 160208 (for days < 10)

Use the Function DATE(year, month, day)

How to extract each part from 160208? Use RIGHT, LEFT and MID Functions as shown below:

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

And that’s not all; use these three string functions appropriately when the order changes: year-month-day, month-year-day, etc.

2) When the date is a regular 8-digits string (2 digits for the day, two digits for the month and four digits for the year)

  1. Select Fixed Width (step 1 of 3)
  2. Define column breaks (step 2 of 3)
  3. Select the Date column in Data Preview and assign the appropriate Date format: Excel MDY, DMY, YMD, MYD, DYM, YDM (step 3 of 3)

Use Text to Column Wizard.

Graphic 6

Text to Columns step 3 of 3; Excel DMY

Convert an Excel Number to Date (A simpler way)

After I posted above article in a LinkedIn group, I received a comment from Rex Langfield (He is an Information Technology and Services Professional.) with a cleaner and smarter solution.

There are so many ways to solve the same problem but the more elegant wins, here you have it…

Assuming the values to be converted are in column A:

In a macro: NewDate = DateValue(Format(Range(“A1”).Value, “00/00/0000″))

Manually in a new cell: =DATEVALUE(TEXT($A1,”00-00-0000”))

Both entries firstly convert the current value into an accepted DATEVALUE string and then the DATEVALUE function does the rest.

Clean and simple!!!

Visit Rex Langfield Profile

Conclusion

An Excel date is the most problematic type of data. The fact is that Dates are perceived as a different type of information.

I think all misunderstandings will stop when you see on the formula bar the number (for example: =40113 ) and the date formatted (10/27/2009) in the cell.

This way you will always remember that you are dealing with numbers too.