Help with formula

[David]

XL2000
This formula works for computing comp time for weekdays and weekends:
=IF(OR(WEEKDAY($A7,1)=1,WEEKDAY($A7,1)=7),B7,IF(I7>8,I7-8,0))

I'd like to reverse the arguments and have IF(I7>8,I7-8,0) at the
beginning, but I keep getting errors. Help me straighten it out.

 

[Frank Kabel]

Hi David just as a gues maybe something like
=IF(I7>8,I7-8,IF(OR(WEEKDAY($A7,1)=1,WEEKDAY($A7,1)=7),B7))

 

[David]

Frank Kabel wrote

Hi David just as a gues maybe something like
=IF(I7>8,I7-8,IF(OR(WEEKDAY($A7,1)=1,WEEKDAY($A7,1)=7),B7))

Wow, that was fast!
No joy, though. Returned FALSE if I didn't work more than 8hr on a weekday.
I want it to return 0 (zero).

 

[Frank Kabel]

Hi David
problem is you have to provide some more details:
- what is the logic you want to implement
- what do your cells A7, B7, I7, etc. contain ( how should anybody in
this NG know)
- what is your expected result for each condition

After this the formula should be quite easy to implement

 

[David]

Frank Kabel wrote

Hi David just as a gues maybe something like
=IF(I7>8,I7-8,IF(OR(WEEKDAY($A7,1)=1,WEEKDAY($A7,1)=7),B7))

But that led to this, which does give desired result, though I'm not sure
how:

=IF(I3>8,I3-8,IF(OR(WEEKDAY($A3,1)=1,WEEKDAY($A3,1)=7),B3,0))

 

[David]

Frank Kabel wrote

problem is you have to provide some more details:

Just for academic reasons now:
A7 contains the date
B7 contains total hours worked
I7 contains the comp time hours
Any hours over 8 on a weekday are comp time
Any hours worked on a weekend are comp time

 

[Frank Kabel]

Hi David
I see you have your answer

 

[David]

David wrote

I7 contains the comp time hours

Oops, I7 contains hours for that day
J7 contains comp time hours

 

[David]

David wrote

Any hours worked on a weekend are comp time

=IF(I7>8,I7-8,IF(OR(WEEKDAY($A7,1)=1,WEEKDAY($A7,1)=7),B7,0))
Oops, but what if I work more than 8hrs on a weekend day?

Formula will actually start in J3 and be copied down. I just grabbed it
from J7 arbitrarily.

 

[David]

Frank Kabel wrote

I see you have your answer

See later post re: working >8hrs on a weekend :-(

 

[Frank Kabel]

Hi David
so please post all your conditions (I saw the cell value description)
and the expected results. So something like
- if weekend and >8 hours then 0
- etc.

 

[David]

Frank Kabel wrote

so please post all your conditions (I saw the cell value description)

Formula in J3 (Comp Time), copied down to J33 (31 days)
1) If A3 = weekday and B3 <=8 then 0
2) If A3 = weekday and B3 >8 then I3-8
3) if A3 = weekend then total hours worked

 

[Frank Kabel]

Hi
try
=IF(WEEKDAY(A3,2)>5,B3,IF(B3<=8,0,I3-8))

 

[David]

Frank Kabel wrote

=IF(WEEKDAY(A3,2)>5,B3,IF(B3<=8,0,I3-8))

Beautiful! Many thanks. I was trying to utilize a formula I was using for
conditional formatting to color weekend cells in the same sheet.

 

[David]

Frank Kabel wrote

=IF(WEEKDAY(A3,2)>5,B3,IF(B3<=8,0,I3-8))

And this let me simplify my conditional format formula to:
=WEEKDAY($A3,2)>5
Filling desired region of sheet. Thanks again!
Two for the price of one

 

[Frank Kabel]

Hi David
thanks for your feedback