# Calculating elapsed time in percentage #UNPARSEABLE

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

• 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

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

Facilityy Professional Services Inc.

http://www.facilityy.com

• thank you all @Emilio Wright @Brent Wilson

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

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

• Thank you Emilio Wright

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

😔

• 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

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

Facilityy Professional Services Inc.

http://www.facilityy.com

• thank you all @Emilio Wright @Brent Wilson

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

• 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

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