Difference between 2 dates, incl weekends, with variable work days

[babryanton]

(I posted this in WS Fxns on July 7 & didn't get any response, so I'
hoping to have better luck in this forum, as I really need an answer
Thank you!!)

I am creating a problem report spreadsheet for work. In col C I have
date/time that the problem started, eg, 23-Jun-2006 15:20 and in col
I have a date/time for when the problem was resolved, eg, 26-Jun-200
15:40.

I need to calculate the amount of hours that we were not available fo
use (due to the problem). This is based on a list of available hours
Eg, on Mondays we are available 19.5 hrs, T-F 22.5 hrs, Sa 14.5 hrs
and Sun 0 hrs.

If I do a straight subtraction, it uses 24 hrs for each day - no
taking into account our available hrs. If I do NETWORKDAYS i
automatically doesn't consider Sat or Sun - and i need it to - and i
does have a DayEnd and DayStart but it assumes they are constants.

I found the day corresponding to the date and thought I could determin
the series of days between it, e.g., if the start day was Friday & th
end day Monday, the series in between is Sat & Sun. Then I could do
lookup on all 4 days in a table & add them all together. But I can'
figure out how to do this!

Can anyone please PLEASE help!?

Thanks so much

 

[Kevin B]

Try Chip Pearson's site at the following URL:

http://www.cpearson.com/excel/datearith.htm

You will have to scroll down through the date functions to the time functions.

I verified the URL before sending this post and its in working order...

 

[babryanton]

Hi there - Thank you for replying!

Before joining this forum, I did review those chip pearson topics
others on his site. The info was very good, but it still didn't addres
how to include weekends & how to calculate when I have variable start
end times throughout the week. THus my question here! THanks

 

[bplumhoff]

Hello,

Enter into A1:B7, for example:
04:00 23:30
01:00 23:30
01:00 23:30
01:00 23:30
01:00 23:30
09:00 23:30
00:00 00:00

Then count_hours(C1,D1,A1:B7) will give you the hours from C1 till D1if
they are included in table A1:B7 (format resulting cell as dd hh:ss,
for example)

The UDF count_hours:

Function count_hours(dt1 As Date, dt2 As Date, _
vwh As Variant) As Date
'Returns time between dt1 and dt2 but counts only
'hours given in table vwh: for example
'04:00 23:30
'01:00 23:30
'01:00 23:30
'01:00 23:30
'01:00 23:30
'09:00 23:30
'00:00 00:00
'This table defines hours to count for each day
'of the week (starting with Monday, 2 columns)
'PB V0.90
Dim dt3 As Date, dt4 As Date, dt5 As Date
Dim i As Long

If dt2 <= dt1 Then
count_hours = 0#
Exit Function
End If

If (Int(dt1) = Int(dt2)) Then
dt3 = Int(dt2) + vwh(Weekday(dt2, 2), 2)
If dt3 > dt2 Then dt3 = dt2
If vwh(Weekday(dt1, 2), 1) = 0 Then
dt4 = Int(dt1)
Else
dt4 = Int(dt1) + vwh(Weekday(dt1, 2), 1)
If dt4 < dt1 Then dt4 = dt1
End If
count_hours = dt3 - dt4
Exit Function
End If

If CDbl(dt1) - Int(CDbl(dt1)) >= vwh(Weekday(dt1, 2), 2) Then
dt3 = 0#
Else
If vwh(Weekday(dt1, 2), 1) = 0 Then
dt3 = Int(dt1)
Else
dt3 = Int(dt1) + vwh(Weekday(dt1, 2), 1)
If dt3 < dt1 Then dt3 = dt1
End If
dt3 = Int(dt1) + vwh(Weekday(dt1, 2), 2) - dt3
End If

If CDbl(dt2) - Int(CDbl(dt2)) <= vwh(Weekday(dt2, 2), 1) Then
dt5 = 0#
Else
dt5 = Int(dt2) + vwh(Weekday(dt2, 2), 2)
If dt5 > dt2 Then dt5 = dt2
If vwh(Weekday(dt2, 2), 1) = 0 Then
dt5 = dt5 - Int(dt2)
Else
dt5 = dt5 - Int(dt2) - vwh(Weekday(dt2, 2), 1)
End If
End If

If Int(dt2) - Int(dt1) > 1 Then
dt4 = 0#
For i = Int(dt1) + 1 To Int(dt2) - 1
dt4 = dt4 + vwh(Weekday(i, 2), 2) - vwh(Weekday(i, 2), 1)
Next i
End If

count_hours = dt3 + dt4 + dt5

End Function

HTH,
Bernd

 

[babryanton]

Thank you very much for your reply! I will look into this and see if it
will help me out.

Thanks again.