# Gmail Date Conversion

#### linaeum66

##### New Member
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!!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Joe4

##### MrExcel MVP, Junior Admin
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
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
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
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
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
Nope, all that seems to be doing is removing the "at" from the statement

#### Rick Rothstein

##### MrExcel MVP
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

##### Well-known Member
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
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"

Replies
3
Views
123
Replies
6
Views
213
Replies
4
Views
373
Replies
3
Views
284
Replies
6
Views
87

Threads
1,171,895
Messages
5,878,076
Members
433,317
Latest member
KenTClark

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back