CODEWITHSUNDEEP
excelexcel-formula
user2626214
user2626214

Reputation: 25

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

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

Upvotes: 2

Views: 936

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

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

enter image description here

• 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,

enter image description here

• 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,

enter image description here

• 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,

enter image description here

• 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()

enter image description here

• 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.

Upvotes: 1

Related Questions