Formula error

burgandee38116

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!

Shaine Greenwood

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

burgandee38116

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

Shaine Greenwood

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)

 

burgandee38116

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