Sum Column Differences between date range

[Kellogg]

Dear Great Minds of Excel,


I have the following data (Except column D)

Column A     Column B        Column C       Column D     Column E     Column F      Column G
Date 1         Start Time 1    End Time 2     Difference    Start Date    End Date       Avg Time to Complete
01/01/14     08:00              08:30             00:30          2/1/14         2/28/14        00:25
01/0714       09:00             09:10              00:10
02/04/14     10:00              10:18              00:18
02/22/14     11:00              11:02              00:02
02/25/14     12:00              12:30              00:30


I want a vba code that will calculate the time difference between Column B and Column C (shown in column D) then calculate the average time to complete tasks over a date range designated in Cells E1 and F1.

Cell E1 = 02/01/14 This is the start of the date range.
Cell F1 = 02/28/14 This is the end of the date range.
Cell G1 = Average time to complete task within date range.

I do not need to have the differences listed in column D.  This is for clarification. The answer should be 00:18 + 00:02 + 00:30 / 3 = 00:25. 

Something using:
Application.Worksheetfunction.sumifs(DateRange1, ......

Any help is always appreciated.  Thanks

 

[Marcelo Branco]

Hi

I'm confused - shouldn't the result be 00:16:40 (16 minutes and 40 seconds)?

00:18 + 00:02 + 00:30 = 00:50 that divided by 3 results in 00:16:40

Could you clarify?

M.

 

[Kellogg]

Marcelo,

You are right. I was having a brain freeze when I was typing.

 

[Marcelo Branco]

Maybe something like this

Dim lastRow As Long


lastRow = Cells(Rows.Count, "A").End(xlUp)


With Range("G2")
    .Value = Evaluate(Replace("=SUMPRODUCT(C2:C@-B2:B@,--(A2:A@>=E2),--(A2:A@<=F2))/3", "@", lastRow))
    .NumberFormat = "hh:mm:ss"
End With

Hope this helps

M.

 

[Marcelo Branco]

Remark
The formula above assumes data beginning in row 2, headers in row 1
So E2= 2/1/14 and F2=2/28/14

M.

 

[Kellogg]

Marcelo,

The number of entries will continue to change as the new are appended for each month to the bottom of the list. The number three will have to change to equal the number of entries between the date range. So if E2 = 1/1/14 and F2 = 1/31/14 3 would be 2. Does this make sense?

 

[Marcelo Branco]

Marcelo,

The number of entries will continue to change as the new are appended for each month to the bottom of the list. The number three will have to change to equal the number of entries between the date range. So if E2 = 1/1/14 and F2 = 1/31/14 3 would be 2. Does this make sense?

Yes, you are right. Wait for a new formula.

M.

 

[Marcelo Branco]

Try

Dim lastRow As Long, numDates As Long


lastRow = Cells(Rows.Count, "A").End(xlUp)


numDates = Evaluate(Replace("=Countifs(A2:A@,"">=""&E2,A2:A@,""<=""&F2)", "@", lastRow))


With Range("G2")
    .Value = Evaluate(Replace("=SUMPRODUCT(C2:C@-B2:B@,--(A2:A@>=E2),--(A2:A@<=F2))", "@", lastRow)) / numDates
    .NumberFormat = "hh:mm:ss"
End With

M.

 

[joeu2004]

Column A  Column B      Column C    Column D    Column E    Column F  Column G
Date 1    Start Time 1  End Time 2  Difference  Start Date  End Date  Avg Time to Complete
01/01/14  08:00         08:30       00:30       2/1/14      2/28/14   00:25
01/07/14  09:00         09:10       00:10
02/04/14  10:00         10:18       00:18
02/22/14  11:00         11:02       00:02
02/25/14  12:00         12:30       00:30

I want a vba code that will calculate the time difference between Column B and Column C (shown in column D) then calculate the average time to complete tasks over a date range designated in Cells E1 and F1.

Sub doit()
Dim dates As Range, startTimes As Range, endTimes As Range
Dim startDate As String, endDate As String
Set dates = Range("a1", Cells(1, 1).End(xlDown))
Set startTimes = dates.Offset(0, 1)
Set endTimes = dates.Offset(0, 2)
startDate = ">=" & Range("e1")
endDate = "<=" & Range("f1")
With Range("g1")
    .NumberFormat = "[h]:mm:ss.000"
    .Value = (WorksheetFunction.SumIfs(endTimes, dates, startDate, dates, endDate) _
        - WorksheetFunction.SumIfs(startTimes, dates, startDate, dates, endDate)) _
        / WorksheetFunction.CountIfs(dates, startDate, dates, endDate)
End With
End Sub

 

[Kellogg]

Marcelo,

This works great. Thanks. This is like Christmas came early. I will use this in many worksheets.