How to convert YYYYMMDD (or Any 8-digit number) to date in Excel

 How to convert YYYYMMDD (or Any 8-digit number) to date in Excel 

It's a very common situation when a date is an input as an 8-digit number like 2020415, and you need to convert it into a date value that Excel can recognize (04/15/2020). In this case, simply changing the cell format to Date won't work - you will get ########## as the result. 

To convert such a number to date, you will have to use the DATE function in combination with RIGHT, LEFT and MID functions. Unfortunately, it is not possible to make a universal formula that will work in all scenarios because the original number can be input in a variety of different formats. For example: 

 

 

Number	Format	Date
15042020	ddmmyyyy	15-Apr-2020
20200415	yyyymmdd
20201504	yyyyddmm

 

 

Anyway, I will try to explain the general approach to converting such number to dates and provide a few formula examples. 

 

For starters, remember the order of the Excel Date function arguments: 

=DATE(year, month, day) 

So, what you need to do is extract a year, month and date from the original number and supply them as the corresponding arguments to the Date function. 

For example, let's see how you can convert number 20200415 (stored in cell A2 with YYYYMMDD) to date 04/15/2020 ( MM/DD/YYYY). 

• Extract the year. It's the first 4 digits, so we use the LEFT function to pick the first 4 characters: LEFT(A2, 4). 

• Extract the month. It's the 5rd and 6th digits, so we employ the MID function to get them MID(A2, 5, 2). Where 5 (second argument) is the start number, and 2 (third argument) is the number of characters to extract. 

• Extract the day. It's the last 2 digits, so we have the Right function to return the last 2 characters: Right(A2,2). 

Finally, embed the above ingredients into the Date function, and you get a formula to convert number to date in Excel: 

=DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2))