# Percentages are showing minus numbers -120%

Options
edited 12/09/19

hello all,

my percentages are going int minus or over 100% . i currently use this formula , can anyone help

=(TODAY() - [Start Date]5) / ([End Date]5 - [Start Date]5)

regards

Carl

Tags:

• ✭✭✭✭✭✭
Options

Strange. Double check that formula. I tested it on my end and got 12% today.

See my screenshot.

• ✭✭✭✭✭✭
edited 05/01/18
Options

Hi Mike,

Given the 25/03/18 date in the original post, I'd say Carl is not using a US date format.

So while he is comparing a 5 day period between the 4th May and 10th May, you are calculating a 6 month period between early April and early October.

Hi Carl,

Your formula is actually doing what it is written to do. Breaking it down using the 28/04/18 date you originally posted:

=(TODAY() - [Start Date]5) / ([End Date]5 - [Start Date]5)

=(28/04/18 - 04/05/18) / (10/05/18 - 04/05/18)

which translates to:

-6 / 6 = -1 (or -100%)

Dividing a negative number (which it will be if TODAY is less than [Start Date]) by a positive number ([End Date] - [Start Date]) will always result in a negative total.

You are seeing -110% as (although the app doesn't let you calculate/access/use it), it's actually calculating a portion of a day (i.e. -6.6 / 6).

What is it you are actually trying to calculate with the above formula?

Kind regards,

Chris McKay

• ✭✭✭✭✭✭
Options

Good observation Chris!

Thanks, for catching that!

• ✭✭✭✭✭✭
Options

All good MIke .

I'm also using the International date format which is why I spotted it.

• Options

Mike and Chris,

basically, i trying to work out percentages between a start date to the end date. i want to percentages to stop at 100% and not go over. Also to remain at 0% if that task start date hasn't started.

i am so used to MS project auto calculating, but love smartsheet..

regards

Carl

• ✭✭✭✭✭✭
Options

You're welcome Carl, glad it got figured out.

• ✭✭✭✭✭✭
Options

OK thanks for the clarification Carl. Just throw a couple of IF statements in there to deal with the above criteria and you're away.

E.g.

=(TODAY() - [Start Date]5) / ([End Date]5 - [Start Date]5) becomes

=IF(TODAY() < [Start Date]5, 0, IF((TODAY() - [Start Date]5) / ([End Date]5 - [Start Date]5) >1, 1, (TODAY() - [Start Date]5) / ([End Date]5 - [Start Date]5)))

Kind regards,

Chris McKay

• Options

It worked it worked... i am so happy thanks again chris...