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?
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.