1

May I know is there any way to add two or more time durations directly? One more thing: they are in the Excel columns for each row which has been defined or evaluated and stored in those columns. They are in dd:hh:mm format in the Excel column stored. Without going for coulmn by column addition for each row, is there any technique which can directly add them by searching their format and put into another column as their summation?

10:23:10 (dd:hh:mm).

Update

PID     T1Nm             T1SD                   T1FD                T1TotTime     T2Nm        T2SD                     T2FD              T2TotTime      TotalTaskDuration

 10      T1      9/27/2012  12:53:03 PM   9/3/2012  1:52:20 PM       23:23:0      T2   9/26/2012  5:55:32 PM    9/14/2012  1:52:20 PM      12:4:3

 20      T1      9/6/2012  8:29:34 AM     9/17/2012  8:59:36 AM      11:0:30      T4   9/26/2012  5:55:32 PM    9/14/2012  1:52:20 PM      12:4:3

Here i just try to give you a diagrammatic views,so that you can understand what i was looking for. There are different time format can have in each column.So we need to catch only the dd:hh:mm format and add them up and need to store it to the last column.Hope this description will help you to understand the whole thing i am looking for.

EDIT: getting total time calculation for all columns are same.Please advice a fix. Unexpected Output

enter image description here

UPDATE

Still error in formatting:

enter image description here

thanks,

Improve this question
29
  • but there are also more columns which contain date values,but i want only such dd:hh:mm format values to be recognized,and collect them and finally add them and stored in that row's any new column.whole operation should be performed ror by row. Commented Dec 14, 2012 at 3:36
  • Summing times in spreadsheet: exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times Commented Dec 14, 2012 at 5:44
  • Manipulating times in VBScript: scriptingmaster.com/asp/date-functions.asp Commented Dec 14, 2012 at 5:48
  • Collecting and summing certain formats: WorksheetFunction.SumIf seems to do the trick. Commented Dec 14, 2012 at 6:08
  • Why do you want to get only dd:hh:mm format values? I am curious as the date format in Excel is much more dependent on your system regional setting formats, unless otherwise you force it to do so. What you are trying to do has to be logically making sense. read this up Commented Dec 14, 2012 at 6:15

1 Answer 1

Reset to default
1

Spreadsheet/code example: http://www.bumpclub.ee/~jyri_r/Excel/Summing_time_differences.xls

Sub CalculateTotalTimes()

  Dim rng As Range
  Dim column_counter As Long
  Dim row_counter As Long
  Dim time_sum() As Variant
  Dim dest_rng As Range
  Dim x As Variant

 Set rng = ActiveSheet.UsedRange

 ReDim time_sum(rng.Rows.Count)

     For row_counter = 1 To rng.Rows.Count
         time_sum(row_counter - 1) = 0
           For column_counter = 1 To rng.Columns.Count - 1 'TotalTaskDuration is the last column, not counted
              If rng(row_counter, column_counter).NumberFormat = "dd:hh:mm" Then
                time_sum(row_counter - 1) = time_sum(row_counter - 1) + rng(row_counter, column_counter).Value2
              End If
           Next
     Next
       x = LBound(time_sum)
         For x = LBound(time_sum) To UBound(time_sum) - 1
            time_sum(x) = time_sum(x + 1)
         Next

   ReDim Preserve time_sum(UBound(time_sum) - 1)

   Set dest_rng = ActiveSheet.Cells(2, rng.Columns.Count)
    Set dest_rng = dest_rng.Resize(UBound(time_sum), 1)
  dest_rng.Value = Application.WorksheetFunction.Transpose(time_sum)

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Yes,on your one.But in my case only the error is total time for each row at the last column showing unexpected values,as I said in the description.my last result screenshot.
Ruut - Any chance to fix the output by taking the time format as [h]:mm:ss. not giving expected data on the last column. Thanks in advance!
Was the problem solved? My VBS skills are non-existent, so I cannot help with debugging here. Sorry for a long Xmas break...
Can you send the table you are working with, or part of it? The example here may lack some essential details.
It contains business data,Thus couldn't
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.