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:
- Pay attention to the date system used in your PC. The above examples assume UK date system.
- 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.
wmfexcel 
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 ?
LikeLike
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,
LikeLike
Thanks 🙂
LikeLike
Hi Caroline
You are welcome!
LikeLike
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.
LikeLike
Hi Andy,
You are welcome.
As you said, credit goes to Rick Rothstein (MVP – Excel). I’ve learned a lot from him indeed. 🙂
Cheers,
LikeLike
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.
LikeLike
You are welcome, Igor.
LikeLike
Hi Rick
How do I turn 13 digits into recognizable excel date like yyyy-mm-dd please I need help with this one
LikeLike
Hi nomvula,
For response from Rick, I guess it’s better for you post your question to Rick’s forum directly:
http://www.excelfox.com/forum/forum.php
Cheers,
LikeLike
Pingback: Dropdown calendar in Excel | wmfexcel
Pingback: Data Scrapping and Cleaning Handouts and data | MCOM 295H Data Visualization
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?
LikeLike
Hi Tony,
To answer your question, I have a question for you.If you have 11114 in A2, what do you expect to return? Nov 1st, 2014 or Jan 11th, 2014?
Do I answer your question?
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?
🙂
LikeLike
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.
LikeLike
Hi Do Tuan Anh,
Good idea of using Date with Left, Right, and probably also Mid. Thanks for sharing!
Cheers,
LikeLike
You can save two characters by using dashes instead of slashes…
=TEXT(A5,”0000-00-00″)+0
LikeLike
Thanks Rick for your comments. I have learnt a lot from you actually! 🙂
LikeLike
You can save two characters by using dashes instead of slashes…
=TEXT(A2,”0000-00-00″)+0
LikeLike
Hi Narayank991,
You are welcome. Hope you like it.
MF
LikeLike
Hi
Thanks for sharing.
Narayan
LikeLike
Pingback: convert 10113 to date format 1/1/2013
Pingback: Date Format: Entering Date as 121214 but want excel displays as 12/12/2014 - Page 2
Pingback: Typing in dates - Page 5
Pingback: Time Conversion (2) | wmfexcel