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





"Chuck Duchon" wrote in message
...
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)

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

"JohnI in Brisbane" wrote in message
...
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" wrote in message
...
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)