Gmail Date Conversion

linaeum66

New Member
Joined
Jul 2, 2017
Messages
25
Hello, this should be an easy one for you guys. Gmail is exporting timestamps to a googlesheet with the default Gmail format of:

"March 16, 2018 at 11:41PM"

Could someone please help me to convert this in an adjacent cell to just the time in military time?

For example:


"March 16, 2018 at 11:41PM" changed to "2341"
Thanks so much!!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,160
Office Version
365
Platform
Windows
For an entry in cell A1 in the format you listed, use this formula:
Code:
=TEXT(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,FIND("at ",A1)+3,LEN(A1))),"AM", " AM"),"PM", " PM")),"HHMM")
Not the prettiest formula, but it works!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,664
Office Version
2010
Platform
Windows
Here is another formula that should work...

=TEXT(MID(REPLACE(A1,LEN(A1)-1,0," "),FIND("at ",A1)+3,8),"hhmm")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,664
Office Version
2010
Platform
Windows
Here is another formula that should work...

=TEXT(MID(REPLACE(A1,LEN(A1)-1,0," "),FIND("at ",A1)+3,8),"hhmm")
Here is variation on the above that uses one less function call...

=TEXT(SUBSTITUTE(REPLACE(A1,LEN(A1)-1,0," ")," at",""),"hhmm")
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Rick,

None of your solutions are converting the time to Miltary or 24 hr time.

I have been playing with Joes code to say the whole sentence by finding the first part with =LEFT(A1,LEN(A1)-7) then joining the 2 functions in a separate cell.

When I try to combine them in one formula for some reason I get the whole sentence with PM on the end
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,664
Office Version
2010
Platform
Windows
Rick,

None of your solutions are converting the time to Miltary or 24 hr time.
They work for me. Perhaps your spaces are not all true spaces. If that is the case, then this modification of my last formula should work for you...

=TEXT(SUBSTITUTE(REPLACE(A1,LEN(A1)-1,0," "),"at",""),"hhmm")
 
Last edited:

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Nope, all that seems to be doing is removing the "at" from the statement
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
That gives me a value error

This was not my post, Joe's code gave a result of 23:41 from the OP's original question, I wanted to join it with the rest of the info, to get March 16, 2018 at 23:41.

I could do this in 2 separate formulas and concatenating them but when I tried to combine the 2 it gave a different answer.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,664
Office Version
2010
Platform
Windows
That gives me a value error
That is good, but then I don't understand why my formula did not work for you. Did you perhaps scoop up a trailing blank space or a trailing Line Feed on the end of the text in the cell?



This was not my post
Sorry, I missed that.



Joe's code gave a result of 23:41 from the OP's original question, I wanted to join it with the rest of the info, to get March 16, 2018 at 23:41.
Just pointing out that the OP did not ask for that. From the OP's original message...

"please help me to convert this in an adjacent cell to just the time in military time"
 

Watch MrExcel Video

Forum statistics

Threads
1,090,035
Messages
5,411,981
Members
403,407
Latest member
craigey1

This Week's Hot Topics

Top