Time Sheet and Comp Time
 

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)

Time Sheet and Comp Time
 

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)

Time Sheet and Comp Time
 

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)

Time Sheet and Comp Time
 

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

Good Luck
Tumbleweed

"Chuck Duchon" wrote in message
...
Johnl,

I spoke too soon! Your solution works for POSITIVE numbers but it won't
handle negatives.

For example

A1=8:00 AM
B1 = 2:00 PM
C1 = 6:00 (Formatted as hh:mm with formula if +B1-A1)
D1 = 2:00 (Formatted as hh:mm with formula of C1-4/24)

However, if the calculation in D1 results in a negative number I get 00:00

A1=8:00 AM
B1 = 11:00 AM
C1 = 3:00 (Formatted as hh:mm with formula if +B1-A1)
D1 = 0:00 (Formatted as hh:mm with formula of C1-4/24)

I need a way to calculate the fact that the person worked 1 hour less than
the planned 4 hours.

I'm suspecting this is happening due to the fact that Excel sees the value
in C1 as a TIME i.e. 3:00 AM versus a value of 3 hours and 0 minutes
(which
is what I want...)

Is there a way to calculate negative hours.

Thanks again for your help.

Chuck


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

Time Sheet and Comp Time
 

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
"Tumbleweed" wrote in message
...
Johnl was on the right track with the 24 hour factor (1hr = 1/24hrs per
day). Took a several revisions, but his should work.
------------------------
=(B1*24)-(A1*24)-4
------------------------

Good Luck
Tumbleweed

"Chuck Duchon" wrote in message
...
Johnl,

I spoke too soon! Your solution works for POSITIVE numbers but it won't
handle negatives.

For example

A1=8:00 AM
B1 = 2:00 PM
C1 = 6:00 (Formatted as hh:mm with formula if +B1-A1)
D1 = 2:00 (Formatted as hh:mm with formula of C1-4/24)

However, if the calculation in D1 results in a negative number I get
00:00

A1=8:00 AM
B1 = 11:00 AM
C1 = 3:00 (Formatted as hh:mm with formula if +B1-A1)
D1 = 0:00 (Formatted as hh:mm with formula of C1-4/24)

I need a way to calculate the fact that the person worked 1 hour less
than
the planned 4 hours.

I'm suspecting this is happening due to the fact that Excel sees the
value
in C1 as a TIME i.e. 3:00 AM versus a value of 3 hours and 0 minutes
(which
is what I want...)

Is there a way to calculate negative hours.

Thanks again for your help.

Chuck


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

Time Sheet and Comp Time
 

then you could just do

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

--
Regards,
Tom Ogilvy


Chuck Duchon wrote in message
...
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
"Tumbleweed" wrote in message
...
Johnl was on the right track with the 24 hour factor (1hr = 1/24hrs per
day). Took a several revisions, but his should work.
------------------------
=(B1*24)-(A1*24)-4
------------------------

Good Luck
Tumbleweed

"Chuck Duchon" wrote in message
...
Johnl,

I spoke too soon! Your solution works for POSITIVE numbers but it
won't
handle negatives.

For example

A1=8:00 AM
B1 = 2:00 PM
C1 = 6:00 (Formatted as hh:mm with formula if +B1-A1)
D1 = 2:00 (Formatted as hh:mm with formula of C1-4/24)

However, if the calculation in D1 results in a negative number I get
00:00

A1=8:00 AM
B1 = 11:00 AM
C1 = 3:00 (Formatted as hh:mm with formula if +B1-A1)
D1 = 0:00 (Formatted as hh:mm with formula of C1-4/24)

I need a way to calculate the fact that the person worked 1 hour less
than
the planned 4 hours.

I'm suspecting this is happening due to the fact that Excel sees the
value
in C1 as a TIME i.e. 3:00 AM versus a value of 3 hours and 0 minutes
(which
is what I want...)

Is there a way to calculate negative hours.

Thanks again for your help.

Chuck


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