"EEE MMM dd HH:mm:ss zzz yyyy" string to dd mm yyyy in excel

0
votes

How to convert "EEE MMM dd HH:mm:ss zzz yyyy" string to dd mm yyyy and mm dd yyyy format using microsoft excel or google sheets. What formula to use and how to Prove/Disprove the reliability of data.

2
excelgoogle-sheetsexcel-formulagoogle-sheets-formula
Use TEXT() formula. like TEXT(A1,"dd mm yyyy") – Harun24HR
Tried it and it says string – Kobe
So your data is actual date or just string? Post some sample data then you desired output. – Harun24HR
Yes actual data is string e.g Wed Mar 02 02:07:01 GMT 2020 would like to turn it into dd-mm-yyyy and mm-dd-yyyy – Kobe

2 Answers

1
votes

Try this formula to google-sheet

=ArrayFormula(TEXT(DATEVALUE(TEXTJOIN("-",TRUE,TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),{2,1,5}*100,100)))),"mmm dd yyyy"))

enter image description here

0
votes

Gave this a try and Excel just won't read the initial string with the TEXT function. So instead we need to split out the info, then pick only the items we need

  1. Use the Text to Columns button to split the data into different columns. I suggest using the Delimited option using a space as the delimiter
  2. Use a TEXTJOIN formula to add the day, month, year into one string in the format you need
  3. Finally format the cell to be Date, just in case something else needs to read this as a date

If you are really keen on making this fully automatic you could use the FIND function inside of the MID function to do the splitting for you (that'll be tricky though)

Good luck