Calculate difference between 2 date and times with average

I have used the formula:
Start time End time
6/9/2007 10:35 AM 6/9/2007 3:30 PM 4:55

Formula Description (Result)
=Text(b1-a1,"d:h

But when I use the text function, I cannot calculate an average. Also, if
you know a way to calculate the difference and exclude weekends...

As for excluding weekends, investigate the NETWORKDAYS formula in Help.

But if your workday is not 24 hours long, and you have holidays to contend
with in addition to weekends, the problem is fairly complex.

Here are the steps:

1. If StartDate and EndDate are on the same date, hours worked = number of
hours from StartTime through EndTime, but correcting for data entry errors
where the a time is earlier than the start of the workday or later than the
end of the workday.

2. If EndDate is the following day, (a) calculate hours worked on StartDate as
StartTime through end of workday. (b) Calculate hours on EndDate = hours from
start of workday through EndTime. Add these 2 times.

3. If EndDate is later than StartDate + 1, (a) calculate hours on StartDate
and EndDate as in Step 2. (b) Then calculate the number of whole workdays
BETWEEN the two dates (i.e. from StartDate + 1 through EndDate - 1, inclusive)
using NETWORKDAYS, then multiply that by the hours in a workday and add the
two together.

The above assumes that StartDate and EndDate are not holidays, but there may
be intervening holidays.

Here is some VBA code that I wrote several years ago to handle this. The
DayStart and DayEnd are hard-coded as 09:00 and 17:00. If that doesn't
correspond with your working hours, you must change those lines marked with
###. It also requires the NETWORKDAYS function in the ATP, so in the VBEditor
you must set a reference to that library (Tools/References). It allows also
for a Holiday list, and the code corrects for data entry errors where your
start or end date is in fact a holiday.

The worksheet formula is simple:

=NetWorkHours(A1,B1,HolidayList)

where the start date+time are in A1, the end date+time in B1, and HolidayList
is a range listing holidays. If Start Date has no time component, it is
assumed to be the BEGINNING of the workday (9:00 AM as written) rather than
0:00. If End Date has no time components, it is is assumed to be the END of
the workday (5:00 PM as written) rather than 24:00. If StartDate > EndDate you
get an error.

The lines between the lines of tildes should be pasted into a standard module
in your workbook.

'~~~~~
Option Explicit

Const DayStart As Double = 9 / 24 '###
Const DayEnd As Double = 17 / 24 '###
Const FullDay As Double = DayEnd - DayStart

Function NetWorkHours(ByVal Date1 As Double, ByVal Date2 As Double, _
Optional Holidays As Range = Nothing) As Variant
'Uses ATP NETWORKDAYS function
Dim D As Long
Dim D1 As Long
Dim D2 As Long
Dim T1 As Double
Dim T2 As Double
Dim Total As Double

If Date1 > Date2 Then
NetWorkHours = CVErr(xlErrNum)
Exit Function
End If

SplitDate Date1, D1, T1
If T1 < 0 Then T1 = DayStart 'no time given

SplitDate Date2, D2, T2
If T2 < 0 Then T2 = DayEnd 'no time given

Total = 0
If D2 = D1 Then
Total = NETWORKDAYS(D1, D1, Holidays) * (T2 - T1)
Else
If NETWORKDAYS(D1, D1, Holidays) Then Total = DayEnd - T1
D = NETWORKDAYS(D1 + 1, D2 - 1, Holidays)
If D > 0 Then Total = Total + D * FullDay
If NETWORKDAYS(D2, D2, Holidays) Then Total = Total + T2 - DayStart
End If
NetWorkHours = Total

End Function

Private Sub SplitDate(DateAndTime As Double, _
D As Long, T As Double)
D = Fix(DateAndTime)
T = DateAndTime - D
If T <> 0 Then
If T < DayStart Then T = DayStart
If T > DayEnd Then T = DayEnd
Else
T = -1
End If
End Sub

'~~~~~


On Wed, 14 Dec 2005 17:21:02 -0800, "Aeryn635"
<[email protected]> wrote:

>I have used the formula:
>Start time End time
>6/9/2007 10:35 AM 6/9/2007 3:30 PM 4:55
>
>Formula Description (Result)
>=Text(b1-a1,"d:h
>
>But when I use the text function, I cannot calculate an average. Also, if
>you know a way to calculate the difference and exclude weekends...