×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Trying to sum time values

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:

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

Hi,

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Trying to sum time values

(OP)
Thanks, but unfortunately I can't get this to work.

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

SUMPRODUCT() is similar to SUMIFS() and COUNTIFS(), but IMHO,it is much more intuitively simple to use.

Also, I would have used Named Ranges or Structured Tables which 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 ROW using 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Trying to sum time values

...also...

Quote:


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

Have you considered using a Pivot Table?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close