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