Unparsable

Aaron M

Hello all,

So, this is my formula; in excel it works and gives me the value that is correct, but in Smartsheet it says unparseable

=IF(AND([Supplier Name]@row = [Supplier Name]1, [Funding Occurrence]@row = "Annual", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row = >"320"), "Bill" ,if(AND([Supplier Name]@row = [Supplier Name]1, [Funding Occurrence]@row = "Quarterly", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row = >"60"),"Bill", "Don't' Bill")))

=IF(([Funding Occurrence]@row = "Annual", "365", IF([Funding Occurrence]@row = "Quarterly", "90", 0)) + [Billing Date]1))))

Both of these formulas are coming back as unparseable.

Also, when I go to drag down the formula, it looks to be anchoring certain cells when they don't need to be.

Sameer Karkhanis

You have brackets at incorrect places in both your formulas and incorrect data type when comparing numbers. Try below,

=IF(AND([Supplier Name]@row = [Supplier Name]1, 
        [Funding Occurrence]@row = "Annual", 
        [Paid (Yes/No)]@row = "", 
        [Billed (Yes/No)]@row = "", 
        TODAY() - [Billing Date]@row =>320), 
        "Bill" ,
        IF(AND([Supplier Name]@row = [Supplier Name]1, 
            [Funding Occurrence]@row = "Quarterly", 
            [Paid (Yes/No)]@row = "", 
            [Billed (Yes/No)]@row = "", 
            TODAY() - [Billing Date]@row =>60), "Bill", "Don't' Bill"
        )
    )

=IF([Funding Occurrence]@row = "Annual", 365, 
    IF([Funding Occurrence]@row = "Quarterly", 90, 0)) + [Billing Date]1

Regarding the anchoring of cells, I don't know what your requirement is but in the above formula when you are comparing values with [Supplier Name]1 then when you drag the formula down the column this will change to [Supplier Name]2, [Supplier Name]3..... and same with [Billing Date]1. In case you want to always compare with the value in the first cell then you will need to change that to [Supplier Name]$1

Aaron M

So, I was able to get the billing date formula to work=IF([Funding Occurrence]@row = "Annual", 365 + [Billing Date]1, IF([Funding Occurrence]@row = "Quarterly", 90, 0 + [Billing Date]1))

But I still can't get my billing formula to work:

=IF(AND([Supplier Name]@row = [Supplier Name]1, [Funding Occurrence]@row = "Annual", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row = >320), "Bill", IF(AND([Supplier Name]@row = [Supplier Name]1, [Funding Occurrence]@row = "Quarterly", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row = >60), "Bill", "Don't' Bill"))))

Genevieve P.

Hi @Aaron M

When you're looking for something that's both equal to and greater than (or equal to and less than) in a formula, you'll want to first put the > or < symbol and then the = sign.

For example, you have:

[Billing Date]@row = >320

But you'll need to use:

[Billing Date]@row >= 320

See: Create and Edit Formulas in Smartsheet

You may also want to use absolute references in your formula if you want one value to stay locked on a specific row:

=IF(AND([Supplier Name]@row = [Supplier Name]$1

Try something like this:

=IF(AND([Supplier Name]@row = [Supplier Name]$1, [Funding Occurrence]@row = "Annual", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row >=320), "Bill", IF(AND([Supplier Name]@row = [Supplier Name]$1, [Funding Occurrence]@row = "Quarterly", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row >=60), "Bill", "Don't' Bill"))))

Cheers,

Genevieve