Please advise if you can help me convert this Excel formula to a Smartsheet formula.

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @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.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    No worries. Welcome to Smartsheet! Formulas can be rather daunting especially when first starting out.


    What is the column type for the MILEAGE column?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. And what type of column is the [Custody Date] column?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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.

Sign In or Register to comment.