time sheet help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Smith - 06 Nov 2006 11:20 GMT

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.

Reply to this Message

Jenn - 06 Nov 2006 23:45 GMT

> 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,
[quoted text clipped - 6 lines]
> AM and 3:00 PM? I would likely have the room to capture such split
> times worked in another column if need be.

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

Reply to this Message

John Smith - 07 Nov 2006 03:48 GMT

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

>> 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,
[quoted text clipped - 24 lines]
> Have a great day!
> Jenni

Reply to this Message

John Smith - 08 Nov 2006 00:41 GMT

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?

Reply to this Message