Please advise if you can help me convert this Excel formula to a Smartsheet formula.
I keep getting #UNPARSEABLE when using the following formula:
=IF([CUSTODY DATE]7>1/1/2000,IF($MILEAGE$2<=([CUSTODY DATE]7+2),(125+75+40),($MILEAGE$2-([CUSTODY DATE]7+2)*15+(125+75+40)), )
I was told to make this correction, however, I am still having the same trouble:
Based on the IF formula provided, $ MILEAGE $ 2 seems to be causing the #UNPARSEABLE error. If this is a column, you will need to change it to [MILEAGE](row number or @row).
Any help is appreciated.
Answers
-
Try changing your date to fit the DATE function. Smartsheet does not recognize "1/1/2000" as a date. It recognizes it as 1 divided by 1 divided by 2000. To use dates in Smartsheet formulas, the DATE function is required.
DATE(yyyy, mm, dd)
=IF([CUSTODY DATE]7>DATE(2000, 1, 1),IF($MILEAGE$2<=[CUSTODY DATE]7+2,125+75+40,($MILEAGE$2-([CUSTODY DATE]7+2)*15+(125+75+40)), )
There is also an extra comma towards the end that is causing the actual #UNPARSEABLE error.
Here is the formula with the DATE function included and the comma (and some unnecessary parenthesis) removed:
=IF([CUSTODY DATE]7 > DATE(2000, 1, 1), IF($MILEAGE$2 <= [CUSTODY DATE]7 + 2,125 + 75 + 40, $MILEAGE$2 - ([CUSTODY DATE]7 + 2) * 15 + (125 + 75 + 40)))
-
You do have to correct your reference to the MILEAGE column.
Looks like you're using a nested IF formula (an IF within an IF) and didn't complete the first IF structure.
So your first if starts: =IF([CUSTODY DATE]7>1/1/2000, (your expression)
Your value if true is another IF which appears complete, it's got an expression, a value if true, and a value if false.
=IF([CUSTODY DATE]2 > 1 / 1 / 2000, IF(MILEAGE2 <= ([CUSTODY DATE]2 + 2), (125 + 75 + 40), (MILEAGE2 - ([CUSTODY DATE]2 + 2) * 15 + (125 + 75 + 40)),
Your value if False on the first IF is missing:
=IF([CUSTODY DATE]2 > 1 / 1 / 2000, IF(MILEAGE2 <= ([CUSTODY DATE]2 + 2), (125 + 75 + 40), (MILEAGE2 - ([CUSTODY DATE]2 + 2) * 15 + (125 + 75 + 40)), 12)), #Value if False should be here)
From the help site:
IF Function
Evaluates a logical expression and returns one value when true or another when false.
Sample Usage
IF([Due Date]1 > [Due Date]2, "Date 1 is Larger", "Date 2 is Larger")
Syntax
IF(logical_expressionvalue_if_true[value_if_false])
- logical_expression—The expression to evaluate. Must be true or false.
- value_if_true—The value or formula to return if the logical expression is true.
- value_if_false—[optional] The value or formula to return if the logical expression is false. If omitted, a blank value is returned.
-
@andyrami Having an IF function nested in the "value if true" portion of another IF function is similar to using "IF(AND(..................),"
I do this quite frequently if I am using multiple variables associated to the same variable. Instead of
=IF(AND(variable 1 = 1, variable 2 = 2), 2, IF(AND(variable 1 = 1, variable 3 = 3), 3))
I just use
=IF(variable 1 = 1, IF(variable 2 = 2, 2, IF(variable 3 = 3, 3)))
Because I am repeating "variable 1" for multiple portions of the remaining IF statements, doing it this way saves a few key strokes. It doesn't seem like much of a difference here, but when you start getting into more complex nested IF's, it can actually be a huge lifesaver.
For example...
I have a very long series of nested IF's, but I only want them to run if a certain cell contains a date. Instead of going through and changing all of my IF statements to have an AND statement and adding that in, I can just wrap the whole thing in a single IF formula that says
=IF(ISDATE([Date Column]@row), run this other long and complex nested IF)
By leaving the third portion of this new IF statement out, it will remain blank if that first IF is not true.
-
Here is a screenshot of the sheet, as I am still getting the error.
-
@Yolanda Brown-Mccutchen Can you also post the exact formula that you are now using?
-
Hi Paul,
I tried the formula you gave but it's not working. I must be doing something wrong. Please be patient with me as this is my first time working with Smartsheet, as well as formulas.
-
No worries. Welcome to Smartsheet! Formulas can be rather daunting especially when first starting out.
What is the column type for the MILEAGE column?
-
Text/Number?
-
Ok. And what type of column is the [Custody Date] column?
-
Ok. So this next step goes back to the DATE function. Because the data in the $MILEAGE$2 cell is if the text/number type, we need to convert it into a date for your formula to run calculations off of.
I am going to assume the format for that data is always going to be mm/dd/yy. If there is a possibility of single digit day or month or four digit year, please let me know so we can rewrite to accommodate. I will also assume that the year is 2000 or greater. Again... Please let me know if we need to accommodate anything else.
To get the year...
=VALUE("20" + RIGHT($MILEAGE$2, 2))
To get the month...
=VALUE(LEFT($MILEAGE$2, 2))
And the day...
=VALUE(MID($MILEAGE$2, 4, 2))
Now we drop these into the appropriate portions of the DATE function...
=DATE(VALUE("20" + RIGHT($MILEAGE$2, 2)), VALUE(LEFT($MILEAGE$2, 2)), VALUE(MID($MILEAGE$2, 4, 2)))
Now we take the above and use it to replace ever reference to $MILEAGE$2...
=IF([CUSTODY DATE]7 > DATE(2000, 1, 1), IF(DATE(VALUE("20" + RIGHT($MILEAGE$2, 2)), VALUE(LEFT($MILEAGE$2, 2)), VALUE(MID($MILEAGE$2, 4, 2))) <= [CUSTODY DATE]7 + 2,125 + 75 + 40, DATE(VALUE("20" + RIGHT($MILEAGE$2, 2)), VALUE(LEFT($MILEAGE$2, 2)), VALUE(MID($MILEAGE$2, 4, 2))) - ([CUSTODY DATE]7 + 2) * 15 + (125 + 75 + 40)))
-
Thank you. I will try this shortly and let you know if it works.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!