- #31
pbuk
Science Advisor
Homework Helper
Gold Member
- 4,970
- 3,220
With the text in column A, enter this in B1:
... and this in C1:
... and copy the formulas down.
This should work, but you will have to play with the x-axis options a bit: you want to set the "Major unit" to 1.15471e-5 (this is the way Excel stores the value 1 s) and with these data set the "Minimum" to 42167.419247685 (this is the value of 10:03:43 on 12 Jun 2015).
If you don't care about what it displays on the axis, it would be easier to ignore the day and just use
to get values in seconds (to 1/1000 s) past midnight.
Code:
=DATE(2015,MONTH(DATEVALUE("1 "&MID(A1,5,3))),VALUE(MID(A1,9,2)))+TIMEVALUE(MID(A1,12,8))+VALUE(MID(A1,21,3)/24/60/60/1000)
Code:
=VALUE(RIGHT(A1,11))This should work, but you will have to play with the x-axis options a bit: you want to set the "Major unit" to 1.15471e-5 (this is the way Excel stores the value 1 s) and with these data set the "Minimum" to 42167.419247685 (this is the value of 10:03:43 on 12 Jun 2015).
If you don't care about what it displays on the axis, it would be easier to ignore the day and just use
Code:
=TIMEVALUE(MID(A1,12,8))*24*60*60+VALUE(MID(A1,21,3)/1000)