Need to conver day/time format to seconds

**EVak** · 09-26-2012, 06:49 PM

Hi All,

New member, first post. Thanks in advance for any help!

I have a data set including a column for the time during the test when the data was collected.
For graphing, I'd like to convert the time stamp in this column into seconds.

Here is a sample of the data:
0d 03:04:4.5
0d 03:04:4.51000022888184
0d 03:04:9.5
0d 03:04:9.51000022888184
0d 03:04:14.5
0d 03:04:14.5100002288818
0d 03:04:19.5
0d 03:04:19.5100002288818
0d 03:04:24.5
0d 03:04:24.5100002288818

Basically, I was recording pulses every 5 seconds.
The test didn't last more than 24 hours, but the days portion of the data field are included as part of the data output from the tester.

Any idea how I can convert this into seconds? I need to keep the resolution up to .01 seconds.
Ideally 0d 03:04:24.5100002288818 = 11064.51

Thanks!

**Jakobshavn** · 09-26-2012, 07:34 PM

With
0d 03:04:19.5100002288818
in A1, use:
=TIMEVALUE(MID(A1,4,256))*24*60*60 to display:
11059.51

**shg** · 09-26-2012, 08:05 PM

Or similarly,

=--TEXT(MID(A4,4,12), "[s].00")

**EVak** · 09-26-2012, 10:44 PM

Thanks for this. It is REALLY helpful.
Apparently, there were some extra spaces before the day value, but I used the TRIM function to clean that up.

shg - the formula worked like a charm. I'd like to understand a little more about how it worked

Jakobshavn - something didn't quite work correctly with this formula...
With 0d 03:04:19.5100002288818 in A1
and using the formula
=TIMEVALUE(MID(A1,4,256))*24*60*60
I get 184.51
It looks like the formula is converting the hours to minutes and the remaining minutes to the result, while ignoring the seconds.
Not sure why.....