# Calculating elapsed time in percentage #UNPARSEABLE

Options

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

=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

• ✭✭✭✭✭
Options

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

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

Options

thank you all @Emilio Wright @Brent Wilson

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

• ✭✭✭✭✭
Options

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.

• Options

Thank you Emilio Wright

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

😔

• ✭✭✭✭✭
Options

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

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

Options

thank you all @Emilio Wright @Brent Wilson

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

• ✭✭✭✭✭
Options

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 !!

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!