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?Image

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!

Excel Tips - Date Conversion

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)


  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 ?


  2. Caroline says:



  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.


    • 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.🙂


  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.


  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


  6. Pingback: Dropdown calendar in Excel | wmfexcel

  7. Pingback: Data Scrapping and Cleaning Handouts and data | MCOM 295H Data Visualization

  8. This is brilliant! Thanks for the information.
    Question: Can I use…
    …as this returns the same result with even fewer characters in the formula?


    • MF says:

      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?🙂


  9. 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.


  10. You can save two characters by using dashes instead of slashes…


  11. You can save two characters by using dashes instead of slashes…


  12. MF says:

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


  13. narayank991 says:


    Thanks for sharing.



  14. Pingback: convert 10113 to date format 1/1/2013

  15. Pingback: Date Format: Entering Date as 121214 but want excel displays as 12/12/2014 - Page 2

  16. Pingback: Typing in dates - Page 5

  17. Pingback: Time Conversion (2) | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s