IF formula & formatting
 

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

IF formula & formatting
 

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

IF formula & formatting
 

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

IF formula & formatting
 

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