Time difference w/o weekends

[AG]

Hi!
Can someone help me with a formula to compute the time difference
between two columns containing "Start Date & Time" and "End Date &
Time". I want to compute my time difference in hours, so I use (End
Date & Time - Start Date & Time)*24. When I compute this time
difference, I do not want to have the non-working time in it, that is,
Friday 6:00 PM till Monday 9:00 AM. My working time definition is
Monday 9:00 AM to Friday 6:00 PM (all through Monday till Friday is
considered working hrs). Please note that both the Start Date & Time
and End Date & Time can fall in Working Time zone or in a Non-Working
time zone. Also, the End Date & Time can be a couple of weeks away
from the Start Date & Time.
Many Thanks for the help.
-AG

 

[Bob Phillips]

=(NETWORKDAYS(A1,A2)-(WEEKDAY(A1,2)<6)-(WEEKDAY(A2,2)<6))*9+
((TIME(18,0,0)-MIN(MOD(A1,1),TIME(18,0,0))*(WEEKDAY(A1,2)<6))+
(MAX(MOD(A2,1),TIME(9,0,0))-TIME(9,0,0)))*24

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[AG]

Hi Bob,
Thanks for your help. I tried the formula. It works but it gives the
wrong results. Let me know if I should provide with more details with
numbers?
Regards,
-AG

 

[daddylonglegs]

Hello AG,

Let's see if I understand your requirements....

On Tuesdays, Wednesdays and Thursday you want to count all 24 hours? so if
start time and date was now [Saturday 24th May 14:00] and end time and date
was the same time next week then the result would be 105?

If so then, assuming start time and date in A2 and end time and date in B2
try this formula:

=SUMPRODUCT(--(WEEKDAY((ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))-0.5)/1440+A2-3/8,2)+MOD((ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))-0.5)/1440+A2-3/8,1)<43/8))/60

Format result cell as number

Note: this works for time periods up to approx 45 days

 

[Bob Phillips]

How can it work, but give wrong results?

In what way is it wrong?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Shane Devenshire]

Hi AG,

I suspect that you are not likely to get any takers on this one. Why - well
in the spreadsheet the formula would be very complicated. I have written a
VBA function for you, then all you need to do is type in the formula
=TimeDiff(A1,B1) and it should return the results you want. You will need
to format the cell to show total hours - one such format is 37:30:55 under
the Time area in Format, Cells. That format shows HH:MM:SS. This function
assumes that A1 contains the starting date and time, B1 the ending date and
time.

Someone else who wants to spend more time can probably shorten my VBA code,
but this seems to work:

Function TimeDiff(STime As Double, ETime As Double) As Double
Dim SDay As Byte
Dim EDay As Byte
Dim MDay As Byte
Dim Time As Double
Dim StartTime As Double
Dim EndTime As Double
Dim StartDate As Long
Dim EndDate As Long

SDay = Weekday(STime, vbMonday)
EDay = Weekday(ETime, vbMonday)
StartDate = Int(STime)
EndDate = Int(ETime)
StartTime = STime - StartDate
EndTime = ETime - EndDate

For I = StartDate To EndDate
If I = StartDate Then 'first day
If SDay = 1 Then 'monday
If SDay = EDay Then 'same first and last day
If EndTime <= 9 / 24 Then
TimeDiff = 0
ElseIf StartTime >= 9 / 24 Then
TimeDiff = ETime - STime
Else
TimeDiff = EndTime - 9 / 24
End If
Else 'different first and last day
If StartTime >= 9 / 24 Then
Time = 1 - StartTime
Else
Time = 15 / 24
End If
End If
ElseIf SDay < 5 Then 'tuesday-thurday
Time = 1 - StartTime
ElseIf SDay = 5 Then 'friday
If SDay = EDay Then 'same start and end date
If StartTime > 18 / 24 Then
TimeDiff = 0
ElseIf EndTime <= 18 / 24 Then
TimeDiff = ETime - STime
Else
TimeDiff = 18 / 24 - StartTime
End If
Else 'different start and end date
If StartTime > 18 / 24 Then
Time = 0
Else
Time = 18 / 24 - StartTime
End If
End If
Else 'saturday or sunday
Time = 0
End If
ElseIf I < EndDate Then 'middle day but not end
MDay = Weekday(I, vbMonday)
If MDay = 1 Then 'monday
Time = Time + 15 / 24
ElseIf MDay < 5 Then 'tuesday-thursday
Time = Time + 1
ElseIf MDay = 5 Then 'friday
Time = Time + 18 / 24
End If
Else 'end date but not if equal to start date
If EDay = 5 Then 'friday
If EndTime <= 18 / 24 Then
TimeDiff = Time + EndTime
Else
TimeDiff = Time + 18 / 24
End If
ElseIf EDay = 1 Then 'monday
If EndTime <= 9 / 24 Then
TimeDiff = Time
Else
TimeDiff = Time + EndTime - 9 / 24
End If
ElseIf EDay < 5 Then 'tuesday - thursday
TimeDiff = Time + EndTime
Else 'sat or sun
TimeDiff = Time
End If
End If
Next I
End Function

You can put this in a module in the Visual Basic Editor in any workbook you
want.

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[daddylonglegs]

Hello Shane,

Your function appears to give the same results as my formula suggestion
above (except my formula shows decimal hours like 26.5 rather than
26:30)....but I note that your function seems to give zero when the start and
end times are on the same date, e.g. if start time/date is 27-May-2008 11:30
and end date 27-May-2008 19:30 result should be 8:00 but function gives zero

 

[AG]

Hi Bob, Daddylonglegs, & Shane,

Thanks very much for the help. I used the formula provided by
daddylonglegs and it works fine for all the cases that I tested except
for one. When the start time and end time is same then the formula
returns #REF!. I am yet to understand the whole formula so I don't
know why that happens but the formula works fine otherwise.

Appreciate everyone's inputs.

Cheers!
- AG

 

[daddylonglegs]

Hello AG,

Nice to hear it works for you (in most cases!)

Here's an improved version, it's more efficient and should give you the same
results as before except it doesn't have any restriction of the period
length....and if start time equals end time it'll return zero.....

=(INT((B2-A2+WEEKDAY(A2-3/8,3)+MOD(A2-3/8,1))/7)*35/8-MIN(35/8,WEEKDAY(A2-3/8,3)+MOD(A2-3/8,1))+MIN(35/8,WEEKDAY(B2-3/8,3)+MOD(B2-3/8,1)))*24

It can probably be shortened with some thought........

 

[AG]

WOW! This is great. I have to admit, I struggled a lot to write the
formula myself before I posted the question on this forum. I tried
writing long formulae with many nested if conditions.

Daddylonglegs,
Many thanks for your help.

Best regards,
- AG

 

[Bernd P]

Hello,

If you like to use a VBA approach:
http://www.sulprobil.com/html/count_hours.html

Regards,
Bernd

 

[AG]

Thanks Bernd