time sheet help

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

I need to analyze the hours worked by employees on certain days off to
determine how many hours, during what would be their normal shift time,
that they actually worked. Here's how I have the columns: A= Time
started, B= time finished, C= the total hours worked, D= normal shift
time start, E= normal shift end time. If someone works from 3:00 AM
(3:00) until 9:00 AM (9:00), leaves but is recalled at 2:00 PM (14:00)
until 5:00 PM (17:00), and the normal shift time is 7:00 AM (7:00) until
3:PM (15:00), F= what formula will capture the hours worked between 7:00
AM and 3:00 PM? I would likely have the room to capture such split
times worked in another column if need be.
 
John said:
I need to analyze the hours worked by employees on certain days off to
determine how many hours, during what would be their normal shift time,
that they actually worked. Here's how I have the columns: A= Time
started, B= time finished, C= the total hours worked, D= normal shift
time start, E= normal shift end time. If someone works from 3:00 AM
(3:00) until 9:00 AM (9:00), leaves but is recalled at 2:00 PM (14:00)
until 5:00 PM (17:00), and the normal shift time is 7:00 AM (7:00) until
3:PM (15:00), F= what formula will capture the hours worked between 7:00
AM and 3:00 PM? I would likely have the room to capture such split
times worked in another column if need be.
Click to expand...


Hello John,
Here is how you would set it up. I hope this helps....

A = 9:00 AM Start Time
B = 5:45 PM End Time
C = '=(B2<A2)+B2-A2' Hours Worked
D =
'=IF(B2>=A2,MAX(0,MIN(B2,"16:30")-MAX(A2,"8:00")),MAX(0,"16:30"-MAX(A2,"8:00"))+MAX(0,MIN(B2,"16:30")-"8:00"))'
(Hours Worked or in this case (CORE HOURS=8:00 - 4:30 PM)
E = '=(B2<A2)+B2-A2 - D2' (Prime time hours or hours worked outside
core hours.)

Try that..mess around with the formula to make it work for you and let
me know the results.

Have a great day!
Jenni
 
Thanks Jenni,
I did as you suggested and customized it to correspond with the times
pertinent to my shifts and it worked great. I am having a bit of a
challenge with the cell formatting when converting from time to decimal.
When multiplied by 24, the sum is the hours plus 24 (ie: 6:00 hours
shows as 30.0). Do you have a solution for this?
John
 
Hi Jenni,
As noted, the formula that you suggested is a hit. If I may, I'll add
another kick. If the hours worked (C) is greater than 5.5 hours, I need
to deduct a one half hour break. Do I do this with an additional column
or can this be incorporated into the formula that I am using? Also,
when I convert the hours to decimal they add 24.0. I corrected this by
subtracting 24.0 at the end of the formula but when the cell amount
should be 0.0, it reads -24. Is there a trick to correct this?
 
Back
Top