CODEWITHSUNDEEP
excel-formula
Narens
Narens

Reputation: 1

changing format date in excel using formula

I have a excel with date format as " Feb 1 2020 1:00AM "

I am trying to convert this format to " 08-10-2019 10:27:50 "

I have tried using text formula but date format remains same its not changing.

Upvotes: 0

Views: 63

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Feb 1 2020 1:00AM is not a date format that Excel recognizes and as such it is a text string and not a number that can be reformatted.

One needs to parse the string to something that Excel will recognize: 1 Feb 2020 1:00 AM

This formula parses the string and turns it into a number that can be formatted as desired:

=--REPLACE(TEXTJOIN(" ",,FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","//b["&{2,1,3,4}&"]")),LEN(A1)-1,0," ")

Then format the date either: mm-dd-yyyy hh:mm:ss or dd-mm-yyyy hh:mm:ss depending on locale.

enter image description here

Upvotes: 1

Related Questions