Formula to convert into a valid date and time

[etuskan]

Hi Everyone,

I have following dates that in excel column L and i need to convert all into a valid date and time format?

[TABLE="width: 249"]
<tbody>[TR]
[TD]December 10, 2017 at 8:29:55 PM GMT[/TD]
[/TR]
[TR]
[TD]January 30, 2018 at 7:37:48 AM GMT[/TD]
[/TR]
[TR]
[TD]January 4, 2018 at 9:02:29 AM GMT
February 8, 2018 at 12:02:29 AM GMT
March 5, 2018 at 9:12:29 AM GMT
April 4, 2018 at 9:22:19 AM GMT
May 4, 2018 at 7:02:39 AM GMT
June 4, 2018 at 9:05:29 AM GMT
July 4, 2018 at 9:12:29 AM GMT
August 4, 2018 at 6:42:09 AM GMT
September 4, 2018 at 9:02:29 AM GMT
October 4, 2018 at 9:02:29 AM GMT
November 4, 2018 at 9:02:29 AM GMT[/TD]
[/TR]
[TR]
[TD]January 8, 2018 at 5:54:24 AM GMT[TABLE="width: 249"]
<tbody>[TR]
[TD]December 12, 2017 at 9:43:34 PM GMT[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[VBA Geek]

Excel 2010
	A	B
1	December 10, 2017 at 8:29:55 PM GMT	Dec 10, 2017 08:29 PM
Sheet3

Cell Formulas
Range	Formula
B1	=--SUBSTITUTE(SUBSTITUTE(A1,"at",""),"GMT","")

 

[etuskan]

Thank you but it still produces the results in text, i want to get the result for 10 December 2017 --> 43079 which is a date. Can you do that?

 

[VBA Geek]

in which language is your excel? if in it is in English then it should work

 

[jtakw]

Thank you but it still produces the results in text, i want to get the result for 10 December 2017 --> 43079 which is a date. Can you do that?

Hi,

Just change the cell format to General or Number.