Calculating elapsed time in percentage #UNPARSEABLE

malik alrayes

I wrote the following formula to get the percentage according to the chart based on today's date, but it didn't work.

Please Help

=IF(OR(ISBLANK([Start date]@row); ISBLANK([End date]@row); ISBLANK(TODAY())); "";IF(TODAY() < [Start date]@row; "Not Yet";IF(TODAY() > [End date]@row; "100%";TEXT((TODAY() - [Start date]@row + 1) / ([End date]@row - [Start date]@row + 1); "0%"))))

It is worth noting that I wrote the formula in Excel and it works fine for me.

But I don't have enough experience to do this on smartsheet

Brent Wilson

@malik alrayes

Be careful about your underlying column.. It looks like you had text and numbers in your original formula going into the column.

It is easier to stay in numbers and format the percentages in the column rather than trying to convert it to text.

try this

IF(OR(ISBLANK([Start date]@row); ISBLANK([End date]@row)); "";IF(TODAY() < [Start date]@row; "0";IF(TODAY() > [End date]@row; "1";(TODAY() - [Start Date]@row + 1) / ([End Date]@row - [Start Date]@row + 1))))

Also

TODAY() - [Start Date]@row + 1) / ([End Date]@row - [Start Date]@row + 1) * 100 + " %"

Is a "Number" adding/joining to a text

I think you need TEXT(TODAY() - [Start Date]@row + 1) / ([End Date]@row - [Start Date]@row + 1) * 100 ) + "%"

If that's the way you want to do it

malik alrayes

thank you all @Emilio Wright @Brent Wilson

 I found the problem that causes the error is to use (,) instead of (;)

Emilio Wright

This should work:

=IF(OR(ISBLANK([Start date]@row); ISBLANK([End date]@row)); "";IF(TODAY() < [Start date]@row; "Not Yet";IF(TODAY() > [End date]@row; "100%";(TODAY() - [Start Date]@row + 1) / ([End Date]@row - [Start Date]@row + 1) * 100 + " %")))

TEXT() is not a valid formula. You can use VALUE() but it's not needed in this example. I replaced the "0%" you had at the end with a " %" and I multiplied the value to give you an accurate percentage from the decimal.

malik alrayes

Thank you Emilio Wright

I used the formula you gave me but unfortunately it didn't work.

😔

Brent Wilson

@malik alrayes

Be careful about your underlying column.. It looks like you had text and numbers in your original formula going into the column.

It is easier to stay in numbers and format the percentages in the column rather than trying to convert it to text.

try this

IF(OR(ISBLANK([Start date]@row); ISBLANK([End date]@row)); "";IF(TODAY() < [Start date]@row; "0";IF(TODAY() > [End date]@row; "1";(TODAY() - [Start Date]@row + 1) / ([End Date]@row - [Start Date]@row + 1))))

Also

TODAY() - [Start Date]@row + 1) / ([End Date]@row - [Start Date]@row + 1) * 100 + " %"

Is a "Number" adding/joining to a text

I think you need TEXT(TODAY() - [Start Date]@row + 1) / ([End Date]@row - [Start Date]@row + 1) * 100 ) + "%"

If that's the way you want to do it

malik alrayes

thank you all @Emilio Wright @Brent Wilson

 I found the problem that causes the error is to use (,) instead of (;)

Brent Wilson

Oh yes.. I was just copying your formula and correcting the logic totally missed the ; in the formula I was copying.. great you figured it out !!

Emilio Wright

@malik alrayes Same as Brent, I just copied your formula since I know that depending on your location, the delimiter could be different.