Timesheet Formula Problem

[Bridie]

I have a number of timesheets for various departments up an
running correctly. However, I am trying to change one to calculat
hours over a 24 hour period where staff are on either a day rate or
night rate depending on the time they start and finish. To complicat
things further some staff's hours are spread over both times i.e. fro
the 7 in the morning to 7 in the evening is at day rate and from 7 i
the evening through to 7 in the morning is at night rate. Start time i
in column R5 and finish time is in column S5 (24 hour clock format).
can calculate the hours between both but I cannot get a working formul
to break this up into day and night hours. After the start time and th
finish time I started using two colums to give me day hours and nigh
hours and I thought I had it sorted until I came to a person wh
started at 1pm (13.00) but finished at 9pm (21.00). I would be gratefu
for any assistance as I can't seem to move any further with this.
IF(AND(R5>DayStart<DayEnd,S5<=DayEnd),S5-R5,"") formula for day hour
in column T5
IF(AND(R5>=DayEnd,S5<=DayStart),24-R5+S5,"") formula for night hours i
column U5
Any help would be much appreciated.
The above are giving me the correct figures on someone starting at 7 p
and finishing at 7am. How do I get to work where someone starts at sa
7am and finishes at 8pm

 

[Frank Kabel]

Hi
for getting the night hours try:
=24*IF(B1>A1,MAX(B1-MAX(A1,19/24),0),1-MAX(A1,19/24)+MIN(B1,7/24))

day hours:
=24*IF(B1>A1,MAX(MIN(B1,19/24)-MAX(A1,7/24),0),MAX(19/24-MAX(A1,7/24),0
)+MAX(B1-7/24,0))


A1: starting time
B1: finishing time