# Problem with if statement

#### Riaang

##### Board Regular
Hi all,

I wonder if this is possible.

I have an excel spreadsheet with a Total column and leftdate or no date in the left column if the record is active.

What I need is a formula that will look at the total column and leftdate column simultanously. If the Amount in the total column is less than 0 and the leftdate is less than 31/12/2004 then I want in the next column LEFT CREDIT. If the amount in the total column is more than 0 and the leftdate is less than 31/12/2004 it must put in LEFT DEBIT. The same rule will apply for records with dates higher than 31/12/2004 or empty it must either give ACTIVE DEBITS or ACTIVE CREDITS.

EXAMPLE:

AMOUNT.....LEFT DATE.....TYPE
-2000.00.......................ACTIVE CREDIT
...300.00....01/01/2003...LEFT DEBIT
..-345.00....01/05/2005...ACTIVE CREDIT (LEFTDATE IN FUTURE)

I hope somebody can assist me.

Thanks

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Use the And function in and If.

e.g.
Code:
``=If(And(A1=??,B1<??),"Left Debit","")``

Replace ?? with your own criteria.

Edited to fix appearance of formula ~ Von Pookie

Formula in C2:
=IF((B2="")+(B2>DATE(2004,12,31)),"ACTIVE ","LEFT ")& IF(A2<0,"CREDIT","DEBIT")

Copy down.
Book4
ABCD
1AMOUNTLEFTDATETYPE
2-2,000.00ACTIVECREDIT
3300.001/1/2003LEFTDEBIT
4-345.001/5/2005ACTIVECREDIT
Sheet1

with amount in A1 and date in B1, try
=CHOOSE((B1<=--"12/31/04")+1,"Active","Left")&CHOOSE((A1>=0)+1," Debit"," Credit")

Thanks a lot all you guys.

I have tested HOTPEPPER's formula and this is working 100%.

Thanks a lot Hotpepper.

Replies
1
Views
259
Replies
0
Views
57
Replies
5
Views
220
Replies
12
Views
200
Replies
2
Views
139

1,203,059
Messages
6,053,299
Members
444,650
Latest member
bookendinSA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back