IF AND OR - an easy one!

[mdma]

Trying to acheive the following :

IF b2 = NEG and C2 = P-1 THEN "TO" ,OR If b2 = POST and c2= " " THEN "TO", ELSE "FROM"

Ive tried to generated what should be an easy form. but to no avail

[mdma]

also, on a date cell, how can i return the week number of the MONTH back?

eg 01/07/06 = week 1
22/07/06 = week 4 and so on..?

[anar_baku]

Use this:
=IF(OR(AND(B2="NEG", C2="P-1"), AND(B2="POST", C2=" ")), "TO", "FROM")

pay particular attention to the - C2=" " - above. C2=" " and C2="" are different. Use the latter if C2 is blank and use the first one if C2 has a space in it.

For your second problem with week numbers I couldn't find a simple solution so instead I used a combination of DAY and VLOOKUP functions. Basically I used DAY to return the day of the week and then used the VLOOKUP to lookup the corresponding week number from a separately created table. I've attached an example file for your convenience.

If anyone has a more noble solution please post!

[SAP PoD]

If you can get away with your dates being text, then

=ROUNDUP((LEFT(A1,2)/7),0)

Assuming your "text" date is in A1