IF formula & formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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 :-)
 
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
 
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
 
Try:

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

Regards,
Fred

rh33a said:
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
Click to expand...
 
Back
Top