If statements

Jared Pietila

I am trying to generate a 1, 2, or 3 depending on if certain cells have text in them. So far I have

=IF([Invoice Number]@row = " ", "1" , "2"), IF([Paid Date]@row = "<>", "3" ,"2")

What I need is

if invoice number is empty a 1.

if invoice number is full and paid date is empty a 2.

if invoice number is full and paid date is full a 3.

Antonio Figueroa

I would use a nested if statement with an "AND"

Please try the formula below

=IF([Invoice Number]@row < 1, 1, IF(AND([Invoice Number]@row > 1, [Paid Date]@row > 1), 3, 2))

Jared Pietila

Getting an Invalid operation prompt

Antonio Figueroa

Please verify that your column names are correct in the formula

Below is a link to a sheet I made and it works

Please review and let me know when you are done with it so I can delete

https://app.smartsheet.com/b/publish?EQBCT=2cd0a8604844452bb0db8f49780c8bb6

Jared Pietila

That works if I dont put a date inside the paid date column (just a random number), but the date column properties is set to date. Im trying to get it to work when the date is selected.

Antonio Figueroa

Try This

=IF([Invoice Number]@row = "", 1, IF(AND([Invoice Number]@row > TODAY() - 365, [Paid Date]@row > TODAY() - 365), 3, 2))

If the dates will be older than 1 year just adjust the - 365 to a further day. like 730 for two years.

Hope this helps.

Antonio Figueroa

Also if Invoice number is not a date change it to

=IF([Invoice Number]@row = "", 1, IF(AND([Invoice Number]@row >1, [Paid Date]@row > TODAY() - 365), 3, 2))

Jared Pietila

=IF([Invoice Number]@row = "", 1, IF(AND([Invoice Number]@row > 1, [Paid Date]@row > TODAY() - 365), 3, 2))

I got it to work, Thank you for the help!

In this case the invoice number was just a number and the paid date was a date selection.