Hour specification and time calculation

[BBert]

Dear readers,

An employee is working shifts and i need to find out WHICH hours of the
shift are intersecting with the daily rate, the evening rate,
the night rate, weekend or holiday rate. Is there a function that give
me that specification of hours?
i.e shift is: 07:00 - 15:00
night rate is: 23:00 - 08:00
Result of function should be: 07:00 - 08:00

or:
shift is: 14:00 - 22:00
evening rate is 18:00 - 23:00
Result of function should be: 18:00 - 22:00

p.s. all start and endtimes are in separate columns.
--
With Kind Regards,
BBert

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"

 

[Karthik]

Hi

Try this..

Say you enter
A1 = Actual start time
B1 = Actual stop time
C1 = Shift start time
D1 = Shift stop time

E1
=IF(IF(C1>A1,IF(C1>B1,IF(D1>A1,IF(D1>B1,""))),IF(C1<B1,IF(D1<A1,IF(D1<B1,""))))
="","",IF(D1-C1>0,IF(B1-A1>0,C1,A1),IF(B1-A1>0,A1,C1)))

F1
=IF(IF(C1>A1,IF(C1>B1,IF(D1>A1,IF(D1>B1,""))),IF(C1<B1,IF(D1<A1,IF(D1<B1,""))))
="","",IF(D1-C1>0,IF(B1-A1>0,B1,D1),IF(B1-A1>0,D1,B1)))

The intersection time period comes in cell E1 and F1.


Thanks
Karthik Bhat
Bangalore

 

[BBert]

On 29 Jan 2006 23:18:30 -0800, Karthik wrote...

Say you enter
A1 = Actual start time
B1 = Actual stop time
C1 = Shift start time
D1 = Shift stop time

E1
=IF(IF(C1>A1,IF(C1>B1,IF(D1>A1,IF(D1>B1,""))),IF(C1<B1,IF(D1<A1,IF(D1<B1,""))))
="","",IF(D1-C1>0,IF(B1-A1>0,C1,A1),IF(B1-A1>0,A1,C1)))

F1
=IF(IF(C1>A1,IF(C1>B1,IF(D1>A1,IF(D1>B1,""))),IF(C1<B1,IF(D1<A1,IF(D1<B1,""))))
="","",IF(D1-C1>0,IF(B1-A1>0,B1,D1),IF(B1-A1>0,D1,B1)))

The intersection time period comes in cell E1 and F1.

Thanks for your solution but it works just partially and i get some
unpredictable results. So i figured out some function in vba, and it
works.
Anyway thanks for your response and with some adjustments it will surely
work.
***************************************
Function BeginTijd(ByVal shStart, ByVal shEnd, _
ByVal LoLimit, ByVal UpLimit)

If shEnd >= UpLimit And shStart >= LoLimit And _
shStart < UpLimit Then
BeginTijd = shStart
Exit Function
End If

If shStart < LoLimit And shEnd >= UpLimit Then
BeginTijd = LoLimit
Exit Function
End If

If shStart < LoLimit And shEnd > LoLimit And _
shEnd <= UpLimit Then
BeginTijd = LoLimit
Exit Function
End If

If shStart >= LoLimit And shStart <= UpLimit And _
shEnd <= UpLimit And shEnd >= LoLimit Then
BeginTijd = shStart
Exit Function
End If
End Function

Function EindTijd(ByVal shStart, ByVal shEnd, _
ByVal LoLimit, ByVal UpLimit)

If shEnd >= UpLimit And shStart >= LoLimit And _
shStart < UpLimit Then
EindTijd = UpLimit
Exit Function
End If

If shStart < LoLimit And shEnd >= UpLimit Then
EindTijd = UpLimit
Exit Function
End If

If shStart < LoLimit And shEnd > LoLimit And _
shEnd <= UpLimit Then
EindTijd = shEnd
Exit Function
End If

If shStart >= LoLimit And shStart <= UpLimit And _
shEnd <= UpLimit And shEnd >= LoLimit Then
EindTijd = shEnd
Exit Function
End If
End Function
***************************************
--
With Kind Regards,
BBert

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"