Calculating numbers of hours worked in pay divisions

[andyiain]

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

 

[Guest]

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.