CODEWITHSUNDEEP
excelexcel-formulatimestamp
Trevor Burger
Trevor Burger

Reputation: 67

Timestamp line to a excel readable timestamp

I have thousands of timestamps in the form shown:

Sun Jul 02 06:00:02 2017 (GMT-04:00)

With Date, Day, and Time all varying. This is a military time stamp ( ie each day counts up to 24 hours)

I have used the following formula to get just the time. But it's not readable by excel. 

=MID($A2,FIND(":",$A2)-2,8)

This results in a value like:

06:00:02

However, this does not help and I have to do much manipulation. I want excel to recognize the date and time so i have a true timeline on my x-axis.

I'd like to get it in the form of

7/2/2017 6:00:02 AM

This way I can have a graph that appears like the following:

Graph Example with desired timeline on x axis

Cheers!!!!

Upvotes: 1

Views: 120

Answers (1)

teylyn
teylyn

Reputation: 35915

Since the weekday and the month are all in three letter abbreviations, you can use the Mid function with an absolute position to extract elements of the date. For just the date you could use

=DATEVALUE(MID(A1,9,2)&"-"&MID(A1,5,3)&"-"&MID(A1,21,4))

For just the time value, you could use

=TIMEVALUE(MID(A1,12,8))

To get the date and time in one value, just add the two and format as you want to see it.

=DATEVALUE(MID(A1,9,2)&"-"&MID(A1,5,3)&"-"&MID(A1,21,4))+TIMEVALUE(MID(A1,12,8))

enter image description here

Upvotes: 2

Related Questions