Multiple IF and And Function Help Needed!!

Hi,

I need help completing an IF or And formula for my data attached.

I have entered a Formula =IF(AND(B2="Debit"),"Debit/Credit's",IF(AND(B2="Standard"),"Standard Invoice",IF(AND(B2="Prepayment"),"Prepayment",IF(AND(B2="Credit"),"Debit/Credit's",1)))) in Cell F2 that returns me a value if cell B2 is a specific Invoice Type. However I need to build on this Formula for results that are Standard Invoice's Only.

So if its a Standard Invoice and Column C is "N" then I need to return "Complete"

and if its "Y" is the tricky part as:

I then need to look at the Column D "Due Date" to see if the Date is less than today's date in H1 and return "Overdue", or If it is between today's date and today's date +7 return "Due within 7 Day's" and finally if it is greater than today's Date + 7 return "Due Greater than 7 Day's".

Im not sure if such a formula is possible but any help I could get on this would be great as this is something I have to manually break out each day for 20,000 + Lines

Thanks in Advance

First of all, you don't need any of the AND functions in your formula - that is used when you have two or more conditions that each have to be satisfied, but in your case you only have one condition each time. So, the formula you have now can be simplified to this (I've re-arranged it slightly):

=IF(OR(B2="Debit",B2="Credit"),"Debit/Credit's",IF(B2="Prepayment","Prepayment",IF(B2="Standard","Standard Invoice","")))

So now we just have to incorporate your other requirements at the end of this formula.

I'm just about to take a break, so I'll look at it later.

Hope this helps.

Pete

Hi Olly,

Thanks so much for looking at this for me. Your formula works for everything apart form the Invoice type "Credit" where it brings in a blank instead of "Debit/Credit's"?

Okay, using the earlier formula that I posted, you can extend it like this:

=IF(OR(B2="Debit",B2="Credit"),"Debit/Credit's",IF(B2="Prepayment","Prepayment",IF(B2="Standard",IF(C2="N","Complete",IF(D2<$H$1,"Overdue",IF(D2<$H$1+7,"Due within 7 days","Due greater than 7 days"))),"")))

Hope this helps.

Pete

Hi Pete,

Thanks so much for looking at this for me. Your formula has worked perfectly.

Thanks
David