convert dd:hh:mm:ss to seconds

[Greg]

I need to change some data to seconds. One of the problems is that some of
the data in the column is a subtotal, so the data is dd:hh:mm:ss where some
of it is hh:mm:ss.

Is there one formula that I can use as part of others (IF, SUMPRODUCT, etc.)
that coverts both dd:hh:mm:ss and hh:mm:ss to seconds?

I currently just multiply hh:mm:ss by 86400 to convert it to seconds.

TIA,
Greg

 

[Luke M]

Multiplying by 86400 will work for both. If days are included, 1 day = 86400
seconds, so your math still works out.

Note that you could also just use a custom format of:

 

[Greg]

Hi Luke:

Sorry, the 86400 doesn't work for my situation. I get a #VALUE! error. Is
there a way to force the cell to a certain format (currently dumped out of
the phone system as "General") to perform the calculation?

For example, I have found the following, but it's too involved for what I am
trying to do, and mainly I don't understand the whole formula:

=--TEXT(SUBSTITUTE(IF(ISERR(--$D$10),'$D$10,--$D$10),":","Jan1900 ",1), "")

TIA

Greg

Multiplying by 86400 will work for both. If days are included, 1 day = 86400
seconds, so your math still works out.

Note that you could also just use a custom format of:

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I need to change some data to seconds. One of the problems is that some of
the data in the column is a subtotal, so the data is dd:hh:mm:ss where some
of it is hh:mm:ss.

Is there one formula that I can use as part of others (IF, SUMPRODUCT, etc.)
that coverts both dd:hh:mm:ss and hh:mm:ss to seconds?

I currently just multiply hh:mm:ss by 86400 to convert it to seconds.

TIA,
Greg

 

[Dave Peterson]

dd:hh:mm:ss
Strip off the dd (the first two characters) and multiply by 24*60*60
then make sure hh:mm:ss is treated as a time (timevalue() in a worksheet
formula???) and multiply by 24*60*60.

=(LEFT(A1,2)*24*60*60)+(TIMEVALUE(MID(A1,4,8))*24*60*60)

And since excel will coerce things that look like times/numbers to
times/numbers:
=(LEFT(A1,2)*24*60*60)+(MID(A1,4,8)*24*60*60)

And grouping nicely:
=(LEFT(A1,2)+(MID(A1,4,8)))*24*60*60

But you could also =sumif() to get the total time:

=sumif(b:b,"Greg, Lastname",c:c)
(if I recall the layout of your data. That text stuff (on the total line) is
text and will be ignored.)

And then multiply that by 24*60*60.

 

[Luke M]

The data you are working with, is it actually a numerical date, or is it just
text that looks like a date?

Since your getting a error, I can only assume that you actually have text
that looks like times. This formula will handle both instances, and transform
into a number, and then convert to seconds.

=(IF(LEN(A2)>8,LEFT(A2,FIND(":",A2)-1)+MID(A2,FIND(":",A2)+1,2)/24+MID(A2,FIND(":",A2,FIND(":",A2)+1)+1,2)/(24*60)+RIGHT(A2,2)/(24*60*60),TIMEVALUE(A2)))*86400


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Hi Luke:

Sorry, the 86400 doesn't work for my situation. I get a #VALUE! error. Is
there a way to force the cell to a certain format (currently dumped out of
the phone system as "General") to perform the calculation?

For example, I have found the following, but it's too involved for what I am
trying to do, and mainly I don't understand the whole formula:

=--TEXT(SUBSTITUTE(IF(ISERR(--$D$10),'$D$10,--$D$10),":","Jan1900 ",1), "")

TIA

Greg

Multiplying by 86400 will work for both. If days are included, 1 day = 86400
seconds, so your math still works out.

Note that you could also just use a custom format of:

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I need to change some data to seconds. One of the problems is that some of
the data in the column is a subtotal, so the data is dd:hh:mm:ss where some
of it is hh:mm:ss.

Is there one formula that I can use as part of others (IF, SUMPRODUCT, etc.)
that coverts both dd:hh:mm:ss and hh:mm:ss to seconds?

I currently just multiply hh:mm:ss by 86400 to convert it to seconds.

TIA,
Greg

 

[Luke M]

Just realized I should have shorted formula to:

=(IF(LEN(A2)>8,LEFT(A2,FIND(":",A2)-1)+TIMEVALUE(MID(A2,FIND(":",A2)+1,256)),TIMEVALUE(A2)))*86400

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

The data you are working with, is it actually a numerical date, or is it just
text that looks like a date?

Since your getting a error, I can only assume that you actually have text
that looks like times. This formula will handle both instances, and transform
into a number, and then convert to seconds.

=(IF(LEN(A2)>8,LEFT(A2,FIND(":",A2)-1)+MID(A2,FIND(":",A2)+1,2)/24+MID(A2,FIND(":",A2,FIND(":",A2)+1)+1,2)/(24*60)+RIGHT(A2,2)/(24*60*60),TIMEVALUE(A2)))*86400


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Hi Luke:

Sorry, the 86400 doesn't work for my situation. I get a #VALUE! error. Is
there a way to force the cell to a certain format (currently dumped out of
the phone system as "General") to perform the calculation?

For example, I have found the following, but it's too involved for what I am
trying to do, and mainly I don't understand the whole formula:

=--TEXT(SUBSTITUTE(IF(ISERR(--$D$10),'$D$10,--$D$10),":","Jan1900 ",1), "")

TIA

Greg

Multiplying by 86400 will work for both. If days are included, 1 day = 86400
seconds, so your math still works out.

Note that you could also just use a custom format of:

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


:

I need to change some data to seconds. One of the problems is that some of
the data in the column is a subtotal, so the data is dd:hh:mm:ss where some
of it is hh:mm:ss.

Is there one formula that I can use as part of others (IF, SUMPRODUCT, etc.)
that coverts both dd:hh:mm:ss and hh:mm:ss to seconds?

I currently just multiply hh:mm:ss by 86400 to convert it to seconds.

TIA,
Greg