Time Sheet and Comp Time

[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)

 

[JohnI in Brisbane]

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

 

[Chuck Duchon]

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

 

[Tumbleweed]

Johnl was on the right track with the 24 hour factor (1hr = 1/24hrs per
day). Took a several revisions, but his should work.

 

[Chuck Duchon]

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

 

[Tom Ogilvy]

then you could just do

=B1-A1-TimeValue("04:00")