Attached the excel spreadsheet. Wonder if it is possible.
Attached the excel spreadsheet. Wonder if it is possible.
You can use this formula in B1:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,A1,TEXT(DATE(RIGHT(A1,4),MATCH(MID(A1,5,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),MID(A1,9,2)),"yyyy-mm-dd"))
then copy down as required.
Hope this helps.
Pete
Try this
Formula:=IFERROR(TEXT(--SUBSTITUTE(MID(A1,5,7)&RIGHT(A1,4)," ",", ",2),"yyyy-mm-dd"),A1)
Or a little-bit shorter
v A B 1 Mon Jun 04 17:34:48 GMT+08:00 2018 2018-06-04 2 2018-06-05 2018-06-05 3 2018-06-04 2018-06-04 4 2018-06-04 2018-06-04 5 2018-06-03 2018-06-03 6 2018-06-06 2018-06-06 7 Tue Jun 05 12:03:13 GMT+08:00 2018 2018-06-05 8 2018-06-05 2018-06-05 9 2018-06-05 2018-06-05
Formula:=IFERROR(TEXT(--(MID(A1,5,6)&", "&RIGHT(A1,4)),"yyyy-mm-dd"),A1)
Last edited by AlKey; 06-18-2018 at 10:43 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
=IFERROR(TEXT(--(MID(S3,5,6)&", "&RIGHT(S3,4)),"yyyy-mm-dd"),S3)
Wow thank you this works like a charm
Thank you everyone!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks