If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Timesheet Formula Problem
I have a number of timesheets for various departments up and
running correctly. However, I am trying to change one to calculate hours over a 24 hour period where staff are on either a day rate or a night rate depending on the time they start and finish. To complicate things further some staff's hours are spread over both times i.e. from the 7 in the morning to 7 in the evening is at day rate and from 7 in the evening through to 7 in the morning is at night rate. Start time is in column R5 and finish time is in column S5 (24 hour clock format). I can calculate the hours between both but I cannot get a working formula to break this up into day and night hours. After the start time and the finish time I started using two colums to give me day hours and night hours and I thought I had it sorted until I came to a person who started at 1pm (13.00) but finished at 9pm (21.00). I would be grateful for any assistance as I can't seem to move any further with this. IF(AND(R5DayStartDayEnd,S5=DayEnd),S5-R5,"") formula for day hours in column T5 IF(AND(R5=DayEnd,S5=DayStart),24-R5+S5,"") formula for night hours in column U5 Any help would be much appreciated. The above are giving me the correct figures on someone starting at 7 pm and finishing at 7am. How do I get to work where someone starts at say 7am and finishes at 8pm. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Timesheet Formula Problem
Hi
for getting the night hours try: =24*IF(B1A1,MAX(B1-MAX(A1,19/24),0),1-MAX(A1,19/24)+MIN(B1,7/24)) day hours: =24*IF(B1A1,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 -- Regards Frank Kabel Frankfurt, Germany confused: I have a number of timesheets for various departments up and running correctly. However, I am trying to change one to calculate hours over a 24 hour period where staff are on either a day rate or a night rate depending on the time they start and finish. To complicate things further some staff's hours are spread over both times i.e. from the 7 in the morning to 7 in the evening is at day rate and from 7 in the evening through to 7 in the morning is at night rate. Start time is in column R5 and finish time is in column S5 (24 hour clock format). I can calculate the hours between both but I cannot get a working formula to break this up into day and night hours. After the start time and the finish time I started using two colums to give me day hours and night hours and I thought I had it sorted until I came to a person who started at 1pm (13.00) but finished at 9pm (21.00). I would be grateful for any assistance as I can't seem to move any further with this. IF(AND(R5DayStartDayEnd,S5=DayEnd),S5-R5,"") formula for day hours in column T5 IF(AND(R5=DayEnd,S5=DayStart),24-R5+S5,"") formula for night hours in column U5 Any help would be much appreciated. The above are giving me the correct figures on someone starting at 7 pm and finishing at 7am. How do I get to work where someone starts at say 7am and finishes at 8pm. --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|