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

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
Joined
Aug 1, 2002
Messages
64,700
Office Version
  1. 365
Platform
  1. 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
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. 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
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. 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
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. 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
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. 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"
 

Forum statistics

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

Which adblocker are you using?

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
Top