Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating numbers of hours worked in pay divisions
Hola, I have a series (couple of hundred thousand spead over several sheets) of start and end dates and times of shifts and I want to be able to calculate for each shift the number of hours worked in each of the different pay divisions we use. These divisions are, daytime (7:00 am to 7:00 pm), evening (7:00 pm to 7:00 am) and Saturday and Sunday and public holiday. For example a shift starting at 8:00 pm on a Sunday and ending at 10:00 am on a Monday would be 4 Sunday hours, 7 night hours and 3 daytime hours. That kinda malarkey. I've been messing around with this for an age and have got some ludicrously overly-complicated nested ifs that will calculate daytime hours but then if I want to add in the Saturday and Sunday portions it will break me. Can anyone help and save a poor wretch like me. Regards, Andy -- andyiain ------------------------------------------------------------------------ andyiain's Profile: http://www.excelforum.com/member.php...fo&userid=8335 View this thread: http://www.excelforum.com/showthread...hreadid=495462 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating numbers of hours worked in pay divisions
Try this link, there are some great tips
that is where the following suggestion came from http://www.exceltip.com/ To determine whether a time represents signing in or out, enter the CHOOSE, MAX, and ROW functions as shown in the following Array formula in column D: {=CHOOSE(MAX((A2=$A$2:$A$9)*(ROW()<ROW($A$2:$A$9) )*((B2&C2)<($B$2:$B$9&$C$2:$C$9)))+1,"Time Out","Time In")} Thus, "Time In" or "Time Out" will be displayed next to each time shown in column C. Then, to calculate the number of hours worked by each person, use the SUM and IF functions as shown in the following Array formula in column E: {=SUM(IF(D2="Time Out",(A2=$A$2:$A$9)*($D$2:$D$9="Time In")*(C2-$C$2:$C$9),0))} Thus, the number of worked hours will be displayed next to the "Time Out" indicator matching each ID. "andyiain" wrote: Hola, I have a series (couple of hundred thousand spead over several sheets) of start and end dates and times of shifts and I want to be able to calculate for each shift the number of hours worked in each of the different pay divisions we use. These divisions are, daytime (7:00 am to 7:00 pm), evening (7:00 pm to 7:00 am) and Saturday and Sunday and public holiday. For example a shift starting at 8:00 pm on a Sunday and ending at 10:00 am on a Monday would be 4 Sunday hours, 7 night hours and 3 daytime hours. That kinda malarkey. I've been messing around with this for an age and have got some ludicrously overly-complicated nested ifs that will calculate daytime hours but then if I want to add in the Saturday and Sunday portions it will break me. Can anyone help and save a poor wretch like me. Regards, Andy -- andyiain ------------------------------------------------------------------------ andyiain's Profile: http://www.excelforum.com/member.php...fo&userid=8335 View this thread: http://www.excelforum.com/showthread...hreadid=495462 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating working hours | Excel Worksheet Functions | |||
CALCULATE HOURS WORKED | Excel Discussion (Misc queries) | |||
Problems calculating total hours | Excel Discussion (Misc queries) | |||
Hours worked | Excel Worksheet Functions | |||
calculate average hours worked | Excel Worksheet Functions |