IF formula & formatting

[JD]

I am trying to do a time sheet using the 24-hour clock. Here is a snapshot of
one day:

IN 8:00
OUT 17:30
LT 1:00
TT 8:30
ST 00:00
OT 00:00

I can't figure out how to write an IF statement that will show Straight Time
@ 8:00 and the OverTime figure and retain the 24-hour clock format. Any ideas?

 

[Frank Kabel]

Hi
do you mean (if this is all in column A and B):
B5:
=MIN(B4,8/24)

B6:
=B4-B5

both cells formated as time

 

[Frank Kabel]

Hi
I would assume that B4 currently conatins the formula
=B2-B1-B3
if this is correct then the following in B5 will work
B5:
=MIN(B4,8/24)

and also my formula for B6 should work. So what does exactly not work
for you. Provide some example data, your expected result and the wrong
result from my formulas

 

[rh33a]

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

 

[Fred Smith]

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]

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]

Try:

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

Regards,
Fred

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