calculating time with 1/4, 1/2/ and 3/4 of an hour increments

[dalanda]

Hello,

Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:

From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.

They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKED-OR- choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER

I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.

Thanks..

 

[Scott Huish]

Is the time rounded from the total or each individual time in/out?

The basic formula for rounding time to the 1/4 hour is this:

=ROUND(A1*96,0)/96

 

[shg]

Are those amounts calculated for each interval, or for each day? If for the day, then

      -A- ---B----
  1       Mon 5/16
  2   In      7:30
  3   Out    10:45
  4   In     11:03
  5   Out    13:51
  6   In     14:12
  7   Out    16:18
  8           8:15

The formula in B8 is

=FLOOR(SUMPRODUCT(B2:B7, -1^(ROW(B2:B7) - ROW(B1))), "1:00")
+ LOOKUP(MOD(SUMPRODUCT(B2:B7, -1^(ROW(B2:B7) - ROW(B1))), "1:00"), {0,7,16,30,45}/1440, {0,1,2,3,4}/96)

 

[dalanda]

Shq,

OMG, that is brilliant!!! It worked except in cases where the employee's shift is overnight - say starting 21:00 pm and ending 7:00 am. In those cases I get #NUM! error.

Also, the daily totals has now changed as well. Daily totals = B8 (solution above) + values in some or none of the cells below:
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
PTO (VACATION)
FAMILY SICK LEAVE
DAILY TOTALS

For instance, using the example from above, the daily totals now reads: 7:12 eventhough only B8 has a value of 8:15 in it. Not sure why. I tested using another where C8 is 9:00 - the daily totals reads 9:36. Not sure why.

I hope that I did not confuse you too much. Please let me if I can clarify anyting.

I am grateful for your assistance and appreciate any more help you can give. This will surely help to minimize some frustrations for me.

Again, thanks.

Dalanda

 

[dalanda]

Is the time rounded from the total or each individual time in/out?

The basic formula for rounding time to the 1/4 hour is this:

=ROUND(A1*96,0)/96

Thanks HOTPEPPER. I really appreciate you taking the time to respond to my post. In this case, Shq solution worked out better.

Dalanda