Gmail Date Conversion

[linaeum66]

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!!

 

[Joe4]

For an entry in cell A1 in the format you listed, use this formula:

=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]

Here is another formula that should work...

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

 

[Rick Rothstein]

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]

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]

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")

 

[Dryver14]

Nope, all that seems to be doing is removing the "at" from the statement

 

[Rick Rothstein]

Nope, all that seems to be doing is removing the "at" from the statement

Assuming cell A1 contains your GMail date, what does this formula display...

=FIND(CHAR(160),A1)

 

[Dryver14]

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]

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"