Convert text date/time to as date time format in excel

Question

Data is extracted from the application. There is a text representation of data/time as

"Wed Nov 30 2022 09:30:00 GMT+0530 (India Standard Time)" (in text)

I am required to format this column as MS data/time type, instead of text

First:

Tried custom format.. but didn't work. not able to teach "Wed" and "GMT..." part

Second:

Tried to break the words as =MID(A1,5,20) [it gives "Nov 30 2022 09:30:00" ]

and then apply. =TIMEVALUE(TEXT(RIGHT(B1,8),"HH:MM:SS"))

It worked and excel was able to understand it in time format as9:30:00 AM

But, when I applied similarly the Date format as =DATEVALUE(TEXT(LEFT(B1,11),"mmm dd yyyy"))

It gave a Value error, not sure what to do next

Finally:

Is there a way to do it in one go? the entire column can be formatted as a valid date and time.

I took inspiration from: [question]: Convert text date/time to a real date time in excel [blog]: https://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309

Kindly advise

Mayukh Bhattacharya · Accepted Answer · 2022-12-07T12:41:47.353

There are many ways to do it, perhaps for now, I have tried this one,

• Formula used in cell B1

=LET(_string,TEXTSPLIT(MID(A1,5,20),," "),
_date,DATE(INDEX(_string,3),MONTH(INDEX(_string,1)&1),INDEX(_string,2)),
_time,TIMEVALUE(INDEX(_string,4)),
_date+_time)

Another way,

• Formula used in cell C1

=DATEVALUE(SUBSTITUTE(LEFT(MID(A1,5,20),11)," ",", ",2))+RIGHT(MID(A1,5,20),8)+0

DATEVALUE() wrapping not required actually,

• Formula used in cell C1

=SUBSTITUTE(LEFT(MID(A1,5,20),11)," ",", ",2)+RIGHT(MID(A1,5,20),8)

Use LET() to make it more readable,

• Formula used in cell C1

=LET(_extract,MID(A1,5,20),
_datepart,LEFT(_extract,11),
_timepart,RIGHT(_extract,8),
SUBSTITUTE(_datepart," ",", ",2)+RIGHT(_timepart,8)+0)

One more sleek way is using TEXTBEFORE() & TEXTAFTER()

• Formula used in cell D1

=SUBSTITUTE(TEXTBEFORE(TEXTAFTER(A1," ")," GMT")," ",", ",2)

Note: Since in Excel Date and Times are stored as number this will return as a number, The integer portion of the date serial number represents the day, and the decimal portion is the time, hence format it accordingly as per your need or regional settings.

Convert text date/time to as date time format in excel

First:

Second:

Finally:

1 Answers1