Time Sheet and Comp Time

  • Thread starter Thread starter Chuck Duchon
  • Start date Start date
C

Chuck Duchon

I am trying to put together a "time sheet" worksheet.

Column A is start time formatted in hh:mm
Column B is end time formatted in hh:mm
Column C has a formula Bx-Cx and is formatted as hh:mm

Now for the problem:

If the person works exactly 4 hours I want to make "comp" time for that day
0
If the person works more than 4 hours I want to calculate the "comp" time as
hours_worked-4
If the person works less than 4 hours I want to calculate the negative
"comp" time as 4-hours_worked

I'm running into a problem with the calculations

for example:

Column A1 = 8:00 AM
Column B1 = 2:00 PM
Column C1 = 6:00 (Formula is B1-A1)
Column D1 = C1-4

I "expect" D1 to show 4 instead it shows 3.78.

Column D is formatted as a number with two decimal places. If I try any of
the time formats I get an invalid cell (all # signs)
 
Chuck,

Excel stores times as fractions of a day.

The last formula should be-

=C1-4/24

The result is 02:00.

Please post again to say how you went. It's good to know whether this
suggested solution worked or required more thought.



Regards,



JohnI
 
Johnl,

Your solution seems to worked. I just need to take the time to build the
"IF" logic to handle the exceptions.

Thanks for the prompt response!

Chuck
 
Johnl was on the right track with the 24 hour factor (1hr = 1/24hrs per
day). Took a several revisions, but his should work.
 
That worked... after dividing the cell containing the time by 24 the result
was a cell that contained the number of hours (i.e. 4:00 AM now looked like
4.00) which is exactly what I wanted.

Thanks!

Chuck
 
Back
Top