IF & OR Statements

[Guest]

Hello, I'm working on a timesheet, and I currently have it working so that if
for ex:

Someone clocks in at 8:00am, lunchs out at 12pm, lunchs in at 1pm, and clock
outs at 5pm, it calculates the time they were on the clock. Also if for that
day SAT, SUN, or Nothing is entered, it calculates no time (or a blank). See
formula below:

=IF(OR(H10="SAT",H10="SUN",H10="OFF",H10="")," ",(SUM(K10-H10)-SUM(J10-I10)))

What I would like to adjust this to do is add if H10="HOLIDAY",
0.333333333333333 is entered (true value) (equivalent to 8 hours), if false
then complete the above checks (from above formula), if ALL false, then " "
(should be outputted).

So basically I want it to work as such: If Holiday = true, enter 8, if false
enter " ", or if Sat, Sun, Off, is true, enter " ", if false calculate
clocking times.

PLEASE HELP

 

[T. Valko]

Try this:

=IF(OR(H10={"","Sat","Sun",Off"}),"",IF(H10="Holiday",TIME(8,0,0),(K10-H10)-(J10-I10)))

Biff

 

[Guest]

Although that didn't work exactly, it led me to the correct formula:

=IF(OR(H15="SAT",H15="SUN",H15="OFF",H15=""),"
",IF(H15="HOLIDAY",0.333333333333333,(SUM(K15-H15)-SUM(J15-I15))))

So, Thanks for your help

 

[T. Valko]

I'm curious as to what didn't exactly work?

Aside from the cell reference:

IF(OR(H10={"","Sat","Sun",Off"})

Is the same as:

IF(OR(H15="SAT",H15="SUN",H15="OFF",H15="")

IF(H10="Holiday",TIME(8,0,0)

Is the same as:

IF(H15="HOLIDAY",0.333333333333333

And you don't need the SUM function.

(K10-H10)-(J10-I10)

Is the same as:

(SUM(K15-H15)-SUM(J15-I15))

But, if you got it workin' that's what counts!

Biff

 

[Fred Smith]

You'd be better off with Biff's statement. The only significant different is you
used row 15, when originally you asked for row 10.

However, Biff's statement is easier to understand, because:
* It puts all the OR conditions together, rather than repeating "H15="
* It uses Time(8,0,0), which is much easier to understand as 8am than 0.33333...
* It doesn't unnecessarily use SUM()