total a time column that is formatted as ddd:hh:mm:ss .ddd=days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all, I am trying to total a time column. It is currently imported as
ddd:hh:mm:ss where ddd = days (not the day of the week). ANy ideas would be
appreciated.
 
I'd use data|text to columns to separate the ddd from the hh:mm:ss.

Then add the two columns and then sum them.

In B11:
=sum(B1:B10)+sum(c1:c10)

Then in B12:
=TEXT(INT(A11),"000")&":"&TEXT(MOD(A11,1),"hh:mm:ss")


Or don't use the data|text to columns and use these two formulas:

with data in A1:A10,
put this in A11:
=SUMPRODUCT(--LEFT(A1:A10,3))+SUMPRODUCT(--RIGHT(A1:A10,8))
And put this in A12:
=TEXT(INT(A11),"000")&":"&TEXT(MOD(A11,1),"hh:mm:ss")
 
Dave,
This is getting me closer to an answer, but I don't have a solution
yet. I get an error when I use all your examples. But I like this sum product
idea and am investigating it further. Thanks
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Showing date as day of week 4
using a time format in a count function 1
If statements query 4
Extracting Numeric Data from a Delimited Text String 5
Formating Day of the week 5
Date Format 2
Calculating Flying hours allowing for GMT or BST 4
Adding up total hours worked on employee schedule using various criteria 0

Back
Top