IF statement with AND plus OR 1

[dcompto]

Excel 2003
I'm having a problem working out a formula for Column P on a monthly timesheet. All my other formulas seem to be OK.

I enter the date for the first day of the month (e.g., 6/1/07) in C60 which formats as June 2007.

Column B contains weekdays (text): Sat, Sun, Mon, Tue, Wed, Thu, Fri, respectively.
Therefore, for June, 2007, it should look something like:

      A               B       ...      P
 3   Date            Day      ...      Hrs Paid
 4                   Sat               
 5                   Sun               
 6                   Mon               
 7                   Tue               
 8                   Wed               
 9                   Thu               
10   06/01/07        Fri               8.00
11   WEEKLY TOTAL
12   Date            Day      ...      Hrs Paid
13   06/02/07        Sat               8.00
14   06/03/07        Sun               8.00
15   06/04/07        Mon               8.00
16   06/05/07        Tue               8.00
17   06/06/07        Wed               8.00
18   06/07/07        Thu               8.00
19   06/08/07        Fri               8.00
20   WEEKLY TOTAL
etc.

A4 thru A10 formulas, respectively, are:
=IF(WEEKDAY($C$60)=7,$C$60,"")
=IF(WEEKDAY($C$60)<1,A4+1,IF(WEEKDAY($C$60)=7,"",IF(WEEKDAY($C$60)=1,$C$60,"")))
=IF(WEEKDAY($C$60)<2,A5+1,IF(WEEKDAY($C$60)=7,"",IF(WEEKDAY($C$60)=2,$C$60,"")))
=IF(WEEKDAY($C$60)<3,A6+1,IF(WEEKDAY($C$60)=7,"",IF(WEEKDAY($C$60)=3,$C$60,"")))
=IF(WEEKDAY($C$60)<4,A7+1,IF(WEEKDAY($C$60)=7,"",IF(WEEKDAY($C$60)=4,$C$60,"")))
=IF(WEEKDAY($C$60)<5,A8+1,IF(WEEKDAY($C$60)=7,"",IF(WEEKDAY($C$60)=5,$C$60,"")))
=IF(WEEKDAY($C$60)<6,A9+1,IF(WEEKDAY($C$60)=7,"",IF(WEEKDAY($C$60)=6,$C$60,"")))

A14 thru A20 formulas, respectively, are:
=A10+1
=+A14+1
=+A15+1
=+A16+1
=+A17+1
=+A18+1
=+A19+1

Now, here's the part with which I'm struggling. Column P needs to be:

(IF A4 >= $C$60) AND (IF B4 <> "Sat" OR "Sun"), ENTER A VALUE OF 8, OTHERWISE LEAVE BLANK

In other words, IF A4 contains a DATE AND IF B4 is not equal to "Sat" OR "Sun", THEN enter a value of 8; otherwise, leave it blank.

Currently, I have the formula
=IF(AND(A6>=$C$60,B6<>"Sat",B6<>"Sun"),8,"")
and my timesheet looks like:

      A               B       ...      P
 3   Date            Day      ...      Hrs Paid
 4                   Sat               
 5                   Sun               
 6                   Mon               8.00 [red]should be blank[/red]
 7                   Tue               8.00 [red]should be blank[/red]
 8                   Wed               8.00 [red]should be blank[/red]
 9                   Thu               8.00 [red]should be blank[/red]
10   06/01/07        Fri               8.00
11   WEEKLY TOTAL
12   Date            Day      ...      Hrs Paid
13   06/02/07        Sat               8.00
14   06/03/07        Sun               8.00
15   06/04/07        Mon               8.00
16   06/05/07        Tue               8.00
17   06/06/07        Wed               8.00
18   06/07/07        Thu               8.00
19   06/08/07        Fri               8.00
20   WEEKLY TOTAL
etc.

TIA for any and all resposnes

 

[Gavona]

Click on the And part of your formula, open up the function wizard and discover which part of the AND is not returning as expected.

It must be to do with A6>=$C$60
OR you have some leading/trailing blanks in B6 (could try Trim(B6) instead of B6 to eliminate that - or simply re-type.

My betting is that you have a space in A6 rather than a blank.


Gavin

 

[yogia]

Hii dcompto:

Try the following formula in cell P4 ...

=IF(AND(ISNUMBER(A4),A4>=$C$60,B4<>"Sat",B4<>"Sun"),8,"")

and copy it to cells P49

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[PeterMoran]

Hi,

Instead of:

=IF(AND(A6>=$C$60,B6<>"Sat",B6<>"Sun"),8,"")

try:

=IF(AND(A6>=$C$60,OR(B6<>"Sat",B6<>"Sun")),8,"")

Good Luck!

Peter Moran

 

[SkipVought]

I question your entire approch.

It is a mistake to try to make a table into a report. It is a mistake to segment your data in any way other than to archive realy old data.

I'd have a starting date, on a separate PARAMETERS sheet and then reference that that in A2 (Row 1 with headings) and A3 and following is just PrevCellRef+1. No breaks for weeks or months -- that's done with a REPORT.

Column B is redundant data, easily derived from the date in a REPORT.

The acrobatics and heroic efforts that are reflected in this post would be totally eliminated with a nice clean REPORT.

Just MNSHO.


Skip,
[sub]
[red][/red]
[/sub]

 

[yogia]

It is a mistake to try to make a table into a report. ....

Very valuable advice indeed!

And to reemphasize ... Database Creation and Report Generation are two separate activities. Database Creation must follow Database rules. Same Database will be used to generate many different reports -- so it is a mistake to setup a Database to suit generation of a Report -- and no it doesn't matter how much you are convinced that this is the only Report that would be ever generated using this Database -- just wait till another Report is needed ... weeks, days, or hours later.

So, yes indeed DON'T let the format of a Report dictate the setup of a Database.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[dcompto]

yogia's suggested formula worked exactly as I needed. (star given)

Skip, I don't get the correlation between my timesheet and a "database" or "report". but, as always, I will take your comments to heart and enjoy digging deeper and learning more. Thanks.

Thanks to all!

 

[SkipVought]

Your TIMESHEET is constructed with REPORT-like characteristics, like...
[tt]
bounded by a date range
subtotals and totals
[/tt]
These charcteristics make it difficult to do what you are asking help for, due to the row subtotals and sub headings.

When you begin with properly stored data in a table, achieveing your objective would be much simpler.


Skip,
[sub]
[red][/red]
[/sub]