Convert YYYYMMDD to DD/MM/YYYY in Excel - 2 Methods

Today we are going to discuss about different methods in excel to convert dates from YYYYMMDD format to DD/MM/YYYY format. Lot to learn, let's dive in.

Here, input data contains 8 characters, in which

First 4 characters are 'Year'

Character 5 and 6 are 'Month'

Character 7 and 8 are 'Day'.

Method - 1:

Formula in C3 cell is,

=LET(dt,B3:B10,DATE(LEFT(dt,4),MID(dt,5,2),RIGHT(dt,2)))

DATE function requires 3 arguments as shown below,

=DATE(year,month,day)

To extract year, we use LEFT function and in number of characters we hardcoded 4.

So, this will pick 4 characters from left.

={"2024";"2024";"2024";"2024";"2024";"2024";"2024";"2024"}

To extract month, we use MID function,

start_num is 5 and num_chars is 2, output of this function will look like below,

={"10";"10";"10";"10";"10";"10";"10";"10"}

To extract day, we use RIGHT function and in number of characters we hardcoded 2.

So, this will pick 2 characters from right.

={"21";"22";"23";"24";"25";"26";"27";"28"}

Then, DATE function will deliver us,

={45586;45587;45588;45589;45590;45591;45592;45593}

To format this as date, select the range and press shortcut key CTRL + SHIFT + 3

or,

select the range and press shortcut key ALT + H + N + S + ENTER.

Method - 2:

Text to Columns - User interface technique, simple and effective but not dynamic.

Select the range B3:B10 and press shortcut key ALT + A + E

In step-1 of Text to Column wizard,

Make sure Delimited option button is selected and click Next.

In step-2 of Text to Column wizard, make sure Tab delimiter check box is checked

and click Next.

In step-3 of Text to Column wizard,

Select the Option button for Date and in drop down select YMD and click Finish.