Changing the date format in excel-CodePudding

I have an excel dataset that has dates in the following example format: 20150102 (yyyy/dd/mm),

I want to change this to the format (dd/mm/yyyy) How can I do this?

CodePudding user response：

Here are few other alternative ways, which you may find useful,

• Formula used in cell B2

=--TEXT(A2,"0000\/00\/00")

• Formula used in cell B3

=TEXT(A2,"0000\-00\-00")*1

• Formula used in cell B4

=TEXT(A2,"0000\-00\-00") 0

• Formula used in cell B5

=TEXT(A2,"0000\-00\-00")/1

• Formula used in cell B6

=--TEXT(A2,"0000\-00\-00")

• Formula used in cell B7

=DATEVALUE(TEXT(A2,"0000-00-00"))

• Formula used in cell B8

=TEXT(CONCAT(MID(A2,{1,2,3,4,5,6,7,8},1)),"0000\/00\/00") 0

• Formula used in cell B9

=TEXT(CONCAT(MID(A2,{1,5,7},{4,2,2})),"0000\/00\/00") 0

• Formula used in cell B10

=TEXTJOIN("/",,MID(A2,{1,5,7},{4,2,2})) 0

• Formula used in cell B11

=TEXT(TEXT(A2,"0000\/00\/00") 0,"dd/mm/yyyy") 0

• Formula used in cell B12

=TEXT(A2,"0000-00-00") 0

Note: Since Dates are stored as numbers in Excel after applying the formulas it will show you as number if the cells are not priory formatted as Excel Dates. Hence if not formatted then please select the cell or range and press CTRL 1 --> Format cells Dialogue Opens --> Number Tab --> Category --> Custom --> and type by removing General as dd/mm/yyyy

CodePudding user response：

If 20150102 is stored as text or number then try below formula to convert it datevalue. Then use proper cell format.

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

Or use TEXT() function to get desired format directly. Try-

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),DAY(RIGHT(A1,2))),"dd/mm/yyyy")