## Trying to sum time values

## Trying to sum time values

(OP)

I have a worksheet that contains three columns

Columns A and B contain identifier strings, and column C contains a time value in the format hh:mm

I am trying to write a function that stores, on a separate summary sheet, the total time where columns A and B contain specific data (e.g. if there are 2 entries where A = "xxx" and B = "yyy" and the time entries are "11:55" and "13:15" the result should be "25:10", so I have used the numberformat as below)

I have this in my main function:

and this is the function:

I have tried several variations of this but it always returns 0:00.

Can anyone kindly point out my errors?

Many thanks

Columns A and B contain identifier strings, and column C contains a time value in the format hh:mm

I am trying to write a function that stores, on a separate summary sheet, the total time where columns A and B contain specific data (e.g. if there are 2 entries where A = "xxx" and B = "yyy" and the time entries are "11:55" and "13:15" the result should be "25:10", so I have used the numberformat as below)

I have this in my main function:

#### CODE -->

Worksheets("Summary").Range("C" & j).Value = addtime(sF, sS) Worksheets("Summary").Range("C" & j).NumberFormat = "[h]:mm"

and this is the function:

#### CODE -->

Function calctime(sF, sS) As Double Dim i As Integer, l As Integer Dim tot As Double tot = 0 l = FindLastRow("Sheet1") For i = 2 To l If (Worksheets("Sheet1").Range("A" & i).Value = sF) And (Worksheets("Sheet1").Range("B" & i).Value = sS) Then tot = tot + Worksheets("Sheet1").Range("C" & i).Value End If Next addtime = tot End Function

I have tried several variations of this but it always returns 0:00.

Can anyone kindly point out my errors?

Many thanks

## RE: Trying to sum time values

First we need to clarify that your time values in column C are Durations and not times of day.

I’m assuming the former.

Second, you do not need any VBA. This can and should be done on the sheet with Excel functions. Since you gave scant information, this is a scant solution:

=SUMPRODUCT((a=“xxx”)*(b=“yyy”)*(c))

...where a is the range reference in column A, b is the range reference in column B and c is the range reference in column C.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Trying to sum time values

Sheet1 has the format:

A = code1, B= code2, C = ElapsedTime (E-D hh:mm) D = StartTime (hh:mm) , E = FinishTime (hh:mm), F = Date (dd/mm/yyyy)

The StartTime and EndTime are added by the VBA formula Now - (Int(Now)) so do not contain the date portion of the value

Monthly there are about 2500 different rows with up to 96 combinations of sF and sS.

can be summarised as totaltime = sum of items in Column C for each combination of A and B

Summary sheet has the format

A = code1, B= code2, C = TotalTime ([h]:mm)

EDIT: Have found I can use the SUMIFS function and so have overcome the problem - thanks for your previous reply

## RE: Trying to sum time values

Also, I would have used

Named RangesorStructured Tableswhich makes formulas nearly self documenting and makes spreadsheet maintenance much more maintainable.Named Ranges on Sheet 1 via

Formulas > Defined Names > Create Names from Selection > Create Names from Selection in TOP ROWusing your headings:So your formula on Sheet2 would look like (notice the three nested sets of parentheses within the function parentheses)...

C2:

=SUMPRODUCT((code1=A2)*(code2=B2)*(ElapsedTime))Also, Splitting the Date from the Start & Finish Times is a bit awkward. StartTime and FinishTime ought to have the Date component to work smoothly. Are there no instances EVER where the Start and Finish are not in the same day? I would think not. Duration will work just as well if not better, if you have spanning days.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Trying to sum time values

Have you considered using a Pivot Table?

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}