# Formula error

edited 12/09/19

Hello! I am trying to make a formula to help track sales and I keep getting an error. I have 3 columns: Prospect Type, Outcome, and Date Sold/Upgraded. I have an if/and formula to count them for each row and if the: outcome=sold, prospect type is WCW Activation, and the date is between 2/1/18 and 2/28/18. I used the formula

=IF(AND([Date Sold/Upgraded]1>=DATEONLY("2/1/18"),[Date Sold/Upgraded]<=DATEONLY("2/28/18"),Outcome1 = "Sold", [Prospect Type]1 = "WCW Activation"), 1, 0)

so that if it matched that criteria it'd put a 1 and if not, a 0. But I keep getting the error "unparseable". Any help would be appreciated!

• Employee

Hello,

The DATEONLY() portion of your formula is the reason for the error. I'd recommend using DATE() instead, and removing the quotes from the date value. Example:

=IF(AND([Date Sold/Upgraded]1>=DATE(2018,2,1),[Date Sold/Upgraded]<=DATE(2018,2,28),Outcome1 = "Sold", [Prospect Type]1 = "WCW Activation"), 1, 0)

More on the DATE function here: https://help.smartsheet.com/function/date

• I tried that formula as you have it here and I still am getting the error "unparseable". Any thoughts?

• Employee
edited 02/23/18

I forgot the row number in one of my cell references! That's what happens when you write formulas without testing them in a sheet first...I tested this one to work in one of my sheets:

=IF(AND([Date Sold/Upgraded]1 >= DATE(2018, 2, 1), [Date Sold/Upgraded]1 <= DATE(2018, 2, 28), Outcome1 = "Sold", [Prospect Type]1 = "WCW Activation"),1,0)

• I apologize I tried it again and it seems to be working! Thank you for your help!