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
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..
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
Office 2010/365
Are those amounts calculated for each interval, or for each day? If for the day, then
The formula in B8 isCode:-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
=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)
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
Like this thread? Share it with others