Dear Friends,

I'm stuck with this formula.. can you please help me?

here is the worksheet:

A1 = 7:00
A2 = 18:00

B1 = IN
B2 = OUT
B3 = MORNING OVER TIME
B4 = AFTERNOON OVERTIME
B5 = TOTAL OVERTIME

what I've done:

B3 = $A$1-B1
B4 = $A$2-B2
B5 = B3+B4

However, for the B3 formula, it keeps showing "######" as the result (for
negative result), therefore, I cannot find the total overtime correctly.

can you help me please?

thank you in advanced.

Rh33a

Excel will calculate negative times, it just won't display them. So your
formulas will work regardless. I expect that your problem is you need to
check for start times after 7am, and end times before 6pm. Something like:

b3 =max(0,$a$1-b1)
b4 =max(0,b2-$a$2)

Remember to format these results as Time.

Regards,
Fred

"rh33a" wrote in message
...
Dear Friends,

I'm stuck with this formula.. can you please help me?

here is the worksheet:

A1 = 7:00
A2 = 18:00

B1 = IN
B2 = OUT
B3 = MORNING OVER TIME
B4 = AFTERNOON OVERTIME
B5 = TOTAL OVERTIME

what I've done:

B3 = $A$1-B1
B4 = $A$2-B2
B5 = B3+B4

However, for the B3 formula, it keeps showing "######" as the result (for
negative result), therefore, I cannot find the total overtime correctly.

can you help me please?

thank you in advanced.

Rh33a

Thank you, Fred. It solved my problem..
but in the process, i found another problem:

our regulation is, if the overtime is less than an hour, won't be calculated
as overtime. for example:

A1 = 7:00
A2 = 18:00
A3 = 1:00

B1 = 6:45 (IN )
B2 = 18.30 (OUT)
B3 = 00:15 (MORNING OVER TIME)
B4 = 00:30 (AFTERNOON OVERTIME)
B5 = 00:45 (TOTAL OVERTIME) -- I want the result to be 0

this is the formula i've put:
=IF(b3<$a$3,0+b4,IF(b4<$a$3,b3+0,b3+b4))

So far, it works when 1 of the b3 and b4 more than 1 hour.. but when both
are less than 1 hour, it doesn't work.

Can you please help me??

Thank you..

I really appreciate your kind help. :)

Warmest regards,

rh33a



"Fred Smith" wrote:

Excel will calculate negative times, it just won't display them. So your
formulas will work regardless. I expect that your problem is you need to
check for start times after 7am, and end times before 6pm. Something like:

b3 =max(0,$a$1-b1)
b4 =max(0,b2-$a$2)

Remember to format these results as Time.

Regards,
Fred

"rh33a" wrote in message
...
Dear Friends,

I'm stuck with this formula.. can you please help me?

here is the worksheet:

A1 = 7:00
A2 = 18:00

B1 = IN
B2 = OUT
B3 = MORNING OVER TIME
B4 = AFTERNOON OVERTIME
B5 = TOTAL OVERTIME

what I've done:

B3 = $A$1-B1
B4 = $A$2-B2
B5 = B3+B4

However, for the B3 formula, it keeps showing "######" as the result (for
negative result), therefore, I cannot find the total overtime correctly.

can you help me please?

thank you in advanced.

Rh33a

Try:

=if(b3+b4<$a$3,0,b3+b4)

Regards,
Fred

"rh33a" wrote in message
...
Thank you, Fred. It solved my problem..
but in the process, i found another problem:

our regulation is, if the overtime is less than an hour, won't be
calculated
as overtime. for example:

A1 = 7:00
A2 = 18:00
A3 = 1:00

B1 = 6:45 (IN )
B2 = 18.30 (OUT)
B3 = 00:15 (MORNING OVER TIME)
B4 = 00:30 (AFTERNOON OVERTIME)
B5 = 00:45 (TOTAL OVERTIME) -- I want the result to be 0

this is the formula i've put:
=IF(b3<$a$3,0+b4,IF(b4<$a$3,b3+0,b3+b4))

So far, it works when 1 of the b3 and b4 more than 1 hour.. but when both
are less than 1 hour, it doesn't work.

Can you please help me??

Thank you..

I really appreciate your kind help. :)

Warmest regards,

rh33a



"Fred Smith" wrote:

Excel will calculate negative times, it just won't display them. So your
formulas will work regardless. I expect that your problem is you need to
check for start times after 7am, and end times before 6pm. Something
like:

b3 =max(0,$a$1-b1)
b4 =max(0,b2-$a$2)

Remember to format these results as Time.

Regards,
Fred

"rh33a" wrote in message
...
Dear Friends,

I'm stuck with this formula.. can you please help me?

here is the worksheet:

A1 = 7:00
A2 = 18:00

B1 = IN
B2 = OUT
B3 = MORNING OVER TIME
B4 = AFTERNOON OVERTIME
B5 = TOTAL OVERTIME

what I've done:

B3 = $A$1-B1
B4 = $A$2-B2
B5 = B3+B4

However, for the B3 formula, it keeps showing "######" as the result
(for
negative result), therefore, I cannot find the total overtime
correctly.

can you help me please?

thank you in advanced.

Rh33a