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

dalanda

New Member
Joined
Jan 3, 2006
Messages
6
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..
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
Are those amounts calculated for each interval, or for each day? If for the day, then

Code:
      -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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top