Calculating shift totals and breaks past midnight

**mazlou** · 06-14-2013, 08:35 PM

Hi Guys

I have a spreadsheet broken down into 15 minute intervals where I calculate wages to a particular area. (staff can start and finish anywhere on the quarter hour)
If the employee works more than 5 hours then an unpaid break is given at the 4 hour mark of the shift for 30 minutes so no wages should be allocated during that time.
The total of all the 15 minute intervals should add up to the total paid to that employee on the day.
Once i have a shift go past midnight no data appears in any of the 15 minute intervals.
I am currently using the following formula to determine if the wage should be allocated to the 15 minute slot

=IF(AND(CL$1>=$X165,CL$1<=$Y165),IF(AND(CL$1>=$Z165,CL$1<=$AA165),0,$J165),0)

but like I said there are variances and for some employees it works and for others it doesn't and I cant figure out why!!

**dredwolf** · 06-15-2013, 02:08 AM

Okay, I started by changing your formulas in Columns X,Y & Z to these: (or ..you could eliminate these 3 columns, as the data is stored in the reference columns, but this may requirw changes to other sheets as well, so I just went this way...)
(X2) =G2
(Y2) =H2
(Z2) =I2
(You already have the columns formatted as TIME, so no need to change that)
Now the question is--- do you really need the 15 minute interval cost of wages...OR, do you want the cost of that employee? (if you want the cost/15 mins, your formulas need extensive revision, if cost the employee cost per shift, not too bad

)

**mazlou** · 06-15-2013, 08:53 PM

thank for your help. I ended up changing the time to a number and writing an if statement =if(X2>Y2,Y2=1,Y2) in column Z. Then any times after midnight calculated properly. I then used pivot tables to sum all the data on the hour for each day.

**dredwolf** · 06-16-2013, 12:49 AM

Gkad you found a solution...
Date and time are a Number, A lot of what you qare trying can be simplified if you treat them as such...years/months/days (days in actuality) are above 1, fractions of days (Hours/Minutes/Seconds) are the 'decimal' part of the number- what comes after the decimal point

If your solution works for you -

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"