## Convert an 8-digit number into Excel-recognizable Date

Have you ever encountered a situation that date is input as 8-digit number instead of the correct date value in Excel?

You cannot perform any proper date related calculation / functions to the 8-digit number in Excel because it is NOT the correct way to input date in Excel.

Don’t ever think that you can simply change the cell format to Date (like DD/MM/YYYY) and make it work.  Try to turn the value 20140401 into date format, you will get ########## as the value is far beyond the Excel limit for date.

No worry.  As long as all these 8-digit numbers follow the YYYYMMDD pattern, there is a way to turn it back to Excel recognizable Date with a formula.  How?

Again, just in case you are not familiar with how Excel treats date:

“Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.” From Excel Help

Tips: That’s why we cannot turn the serial numbers 20140401 into a date.  The date limit so far reaches at 31/12/9999 which is a serial number of 2958465.

Before we go to the magical formula, let’s explore a few cases of inputting date in Excel:

If we input something like 2013/12/31 or 2014/04/01 directly in a cell, Excel is smart enough to guess your intention (most of the time) and stores your input as date value (and format it as date).  However if you start with an apostrophe coma (‘), Excel knows that you are going to input a Text.

Take a look at the screen shot below, you may see the common ways in input Date by using “\” or “-” as the delimiter.

See!? If we wish to convert a value stored as text back to a value, we may apply a mathematical operation to it.

Well, it looks promising to convert 20140401 into the correct date value intended if we could turn it into a text string “2014/04/01”.  Isn’t it?

Here’s a simple solution:

=TEXT(A2,”0000\/00\/00″)+0 ‘Format the result as Date

where A2 is the 8-digit number

## Why it works?

The TEXT function converts a value to text in a specific number format.  In this case, the specific format in the 2nd argument of the function: “0000\/00\/00” turns an 8-digit number to “0000/00/00”.  E.g. it turns 12345678 into “1234/56/78”; 20131231 to “2013/12/31”.

Note: “\” is needed in the argument to tell Excel that the symbol “/” is intended as a part of the resulting text string.

As mentioned before, certain texts (being interpreted as number stored as text) may be converted to number by mathematical operation.  As as result, the +0 turns the “2013/12/13” into a value of 41639.  Format the value 41639 to Date (DD/MM/YYYY) gives us exactly the desired date result.  However “1234/56/78” is not recognized as a “number stored as text” by Excel.  By adding zero to a text string yields #VALUE!

Did you notice that it works too if your 8-digit number follows the DDMMYYYY pattern, with just a little trick?

Tips: If you want to keep the original input but still want to perform date related calculation, wrap the TEXT formula directly (you may skip to +0 in this case).

• =TEXT(B4,”0000\/00\/00″)-TEXT(A4,”0000\/00\/00″)
• =DATEDIF(TEXT(A4,”0000\/00\/00″),TEXT(B4,”0000\/00\/00″),”D”)
• =WEEKDAY(TEXT(A4,”0000\/00\/00″),2)

Notes:

1. Pay attention to the date system used in your PC.  The above examples assume UK date system.
2. If you are not sure, do the following test:

DO NOT do the test with a date like 01/04/2014 as it could mean Jan 4, 2014 in US or Apr 1, 2014 in UK.

### How about date stored as text?

For other ways to convert dates stored as text into a serial date, please feel free to take a look at Fixing Trouble Dates.

This entry was posted in Excel Tips, Formula and tagged , . Bookmark the permalink.

### 25 Responses to Convert an 8-digit number into Excel-recognizable Date

1. sandip says:

Thanks for this tutorial however i want something i can apply the formula in the cell itself where i put the 8 digit number and it will be converted to the date format. Is that possible ?

Like

• MF says:

Hi sandip,
I believe your requirement is possible with VBA, which however is not my expertise.
May I suggest you search or post your question to http://www.mrexcel.com/forum/
(my favorite excel forum).
Hope it helps.
Cheers,

Like

2. Caroline says:

Thanks 🙂

Like

• MF says:

Hi Caroline
You are welcome!

Like

3. Andy L. says:

Hugely helpful – seems like life is spent dealing with data reformatting issues! This info and the use of the hyphen contributed by RR helped me solve a big issue with my data! Thanks.

Like

• MF says:

Hi Andy,
You are welcome.
As you said, credit goes to Rick Rothstein (MVP – Excel). I’ve learned a lot from him indeed. 🙂
Cheers,

Like

4. Igor says:

Wow, thanks MF!
I found this just in time. I imported employees table from our ERP system to Excel and hire date is like 20140514, and I thought how to solve this, so now I know this formula will do that.

Like

• MF says:

You are welcome, Igor.

Like

5. nomvula says:

Hi Rick

How do I turn 13 digits into recognizable excel date like yyyy-mm-dd please I need help with this one

Like

6. This is brilliant! Thanks for the information.
Question: Can I use…
=TEXT(A2,”0-00-00″)+0
…as this returns the same result with even fewer characters in the formula?

Like

• MF says:

Hi Tony,
If you have 11114 in A2, what do you expect to return? Nov 1st, 2014 or Jan 11th, 2014?

ooops… I think I have misunderstood your question. Yes I think you may save one character as per your suggestion but you have to be very careful that it works only for DDMMYY input. It does not work for YYMMDD input by a formula of =Text(A2,”00-00-0″)+0

On the other hand, my concern is that if you allow your user to input either 1 digit or 2 digits for the date, it could create confusion and probably inconsistent input which, at the end, may jeopardize your result.

So what so eager to do it with just one character saved?
🙂

Like

7. Do Tuan Anh says:

Date with this format ( 8-digit number) is my prefer data type. To ignore all error case, i always use function DATE, with Left and Right, so the result is true no matter what US or UK system.

Like

• MF says:

Hi Do Tuan Anh,
Good idea of using Date with Left, Right, and probably also Mid. Thanks for sharing!
Cheers,

Like

8. You can save two characters by using dashes instead of slashes…
=TEXT(A5,”0000-00-00″)+0

Like

• MF says:

Thanks Rick for your comments. I have learnt a lot from you actually! 🙂

Like

9. You can save two characters by using dashes instead of slashes…
=TEXT(A2,”0000-00-00″)+0

Like

10. MF says:

Hi Narayank991,
You are welcome. Hope you like it.
MF

Like

11. narayank991 says:

Hi

Thanks for sharing.

Narayan

Like

12. Pingback: Typing in dates - Page 5

13. Pingback: Time Conversion (2) | wmfexcel