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

Hi Greg,

Yes, there is a formula that you can use to convert both dd:hh:mm:ss and hh:mm:ss to seconds. Here's how you can do it:

1. For dd:hh:mm:ss format, you can use the following formula:
   
   Code:

   =LEFT(A1,FIND(":",A1)-1)*86400+MID(A1,FIND(":",A1)+1,FIND(":",A1,FIND(":",A1)+1)-FIND(":",A1)-1)*3600+MID(A1,FIND(":",A1,FIND(":",A1)+1)+1,FIND(":",A1,FIND(":",A1,FIND(":",A1)+1)+1)-FIND(":",A1,FIND(":",A1)+1)-1)*60+RIGHT(A1,2)

   This formula extracts the days, hours, minutes, and seconds from the cell and converts them to seconds. You can replace A1 with the cell reference that contains the data.
   
2. For hh:mm:ss format, you can use the formula that you are currently using, which is:
   
   Code:

   =A1*86400

   This formula multiplies the time value by the number of seconds in a day to convert it to seconds.
   
   To use these formulas as part of other formulas like IF and SUMPRODUCT, you can simply replace A1 with the cell reference that contains the data in those formulas.
   

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:
[s]
--
Best Regards,

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


"Greg" wrote:

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

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), "[s]")

TIA

Greg



"Luke M" wrote:

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:
[s]
--
Best Regards,

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


"Greg" wrote:

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

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.





Greg wrote:

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

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!*


"Greg" wrote:

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), "[s]")

TIA

Greg



"Luke M" wrote:

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:
[s]
--
Best Regards,

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


"Greg" wrote:

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

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!*


"Luke M" wrote:

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!*


"Greg" wrote:

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), "[s]")

TIA

Greg



"Luke M" wrote:

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:
[s]
--
Best Regards,

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


"Greg" wrote:

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