addition of time values using vbscript

Question

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,

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. — CodeLover, Dec 14 '12 at 03:36
Summing times in spreadsheet: http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/ — Jüri Ruut, Dec 14 '12 at 05:44
Manipulating times in VBScript: http://www.scriptingmaster.com/asp/date-functions.asp — Jüri Ruut, Dec 14 '12 at 05:48
Collecting and summing certain formats: `WorksheetFunction.SumIf` seems to do the trick. — Jüri Ruut, Dec 14 '12 at 06: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](http://excelsemipro.com/2011/06/regional-date-formats-in-excel/) — bonCodigo, Dec 14 '12 at 06:15
That's the requirement,I have to show the duration of each task in dd:hh:mm format and their summed values too in the last column. as in my case description. — CodeLover, Dec 14 '12 at 06:19
Then sort your TASKS with a unique identifier or a group identifier. Instead of checking date time formats. Isn't it possible to mark your tasks? You have a PID, TID, why not use that to sum of the times? — bonCodigo, Dec 14 '12 at 06:21
Didn't get your point? yes i can identify the tasks by their numbers at all.But some columns may not have values in the "T1TotTime" column. So we need to take care it also... — CodeLover, Dec 14 '12 at 06:24
Don't worry about that some columns not having `T1TotTime`. You have a PID, TID, you can either get time sum by PID or TID or combined key easily. VBA Binary search is faster as long as Code to Sheet traffic is minized. To say you get your input data as a bulk, process it in VBA then put it back. I am talking from VBA, but same can be achieved in VBS. It is the logic that matters. [Check this post out](http://stackoverflow.com/questions/13528333/how-to-search-and-extract-certain-values-in-cells-vba/13530138#13530138) I believe this sort of a routine will work for you. — bonCodigo, Dec 14 '12 at 06:29
Here is one question to you, do you want the total time for a `Total Task Duration = T1TotTime + T2TotTime + T3TotTime + ... + TnTotTime` or do you want the programme to **also** calculate each elaposed time for e.g. T1 (T1SD,T1FD) = T1TotTime across all rows, T2 (T2SD, T2FD) = T2TotTime...etc? If so this is just a matrix calcualtion. You can achieve a manual output using `SUMIF` or `SUMIFS` or writing a small code snippet to get these row, column totals. Correct me if I am talking with the wrong picture in head. — bonCodigo, Dec 14 '12 at 06:38
Can i have a demo code? as your suggested post didnot work for data values? — CodeLover, Dec 14 '12 at 06:38
Yes the suggested post is not for *DATE* values. So can you confirm, that you are looking for such a routine work to be done? means, Row, Col totals to be calculated? — bonCodigo, Dec 14 '12 at 06:39
yes,you are right. but i need only the summations.As duration of each task i have done by other scripts. I need a vbscript routine. — CodeLover, Dec 14 '12 at 06:40
Then it makes things even straightforward. Let's get going. I am personally not much familiar with VBScript. So I will post a VBA code. Then we will work out on the VBScript code. :) — bonCodigo, Dec 14 '12 at 06:43
Are we talking of time differences (ending time-starting time) or sum of times? For me it seems to be the first case. — Jüri Ruut, Dec 14 '12 at 06:44
sum of times which are in the "T1TotTime".. we need to sum up T1TotTime+T2TotTime + .... +TNTotTime — CodeLover, Dec 14 '12 at 07:02
Didn't look around the "corner" of the code window... Sorry. — Jüri Ruut, Dec 14 '12 at 07:33
Hope you understood what i was looking for.If any confusion please ask me.I will be glad to clear that. — CodeLover, Dec 14 '12 at 07:47
@user1897085 can you please update your sample data with 10 rows? And also in `T2Num` column there's `T4`, is it a typo? — bonCodigo, Dec 14 '12 at 08:27
I have provided 2X2 matrix data here. So leave that concern to check the data type. Now i removed those things, waiting for the addition of the duration and put it to the final column.T=1 to 36. Where "TNTotTime" will be of same type "dd:hh:mm" format. we just need them till we get a blank or null values for any N. Say N=6 for one row,has NUll data,then for that row we need to add T1 +..+T5. That's it — CodeLover, Dec 14 '12 at 08:59
t2,t4 are nothing but the tasks names.. you can take them as s3,s2 whatever.. — CodeLover, Dec 14 '12 at 10:42
@user1897085 I do have a workaround for you. And I have to use my **earlier** elaposed time code snippet as a function for this code. Otherwise Excel doesn't recognize your 23:50:00 as a `dd:hh:mm` **ISN'T THAT THE REASON** you wanted to find cells that contain time value of certain format? Well well!!! — bonCodigo, Dec 14 '12 at 12:04
Humm, you are correct! But in my script i will do the addition at last.Not at the elapse time calculation. :-) you do what you suggested.I will then manage it. — CodeLover, Dec 14 '12 at 12:08

Jüri Ruut · Accepted Answer · 2012-12-14T20:47:01.157

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

edited Dec 14 '12 at 20:47

answered Dec 14 '12 at 05:34

Jüri Ruut

2,500
17
20

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. – CodeLover Dec 17 '12 at 17:44
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! – CodeLover Dec 19 '12 at 16:02
Was the problem solved? My VBS skills are non-existent, so I cannot help with debugging here. Sorry for a long Xmas break... – Jüri Ruut Dec 29 '12 at 10:14
Can you send the table you are working with, or part of it? The example here may lack some essential details. – Jüri Ruut Dec 29 '12 at 19:24
It contains business data,Thus couldn't – CodeLover Dec 29 '12 at 19:30
Erase personal data and change actual time values. I'd like to take a look at formatting. Headers + some rows will do. – Jüri Ruut Dec 29 '12 at 19:43

addition of time values using vbscript

1 Answers1