Sign in to join the conversation:
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
Strange. Double check that formula. I tested it on my end and got 12% today.
See my screenshot.
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:
=(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
Good observation Chris!
Thanks, for catching that!
All good MIke .
I'm also using the International date format which is why I spotted it.
Mike and Chris,
thanks for your support..
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..
You're welcome Carl, glad it got figured out.
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)))
It worked it worked... i am so happy thanks again chris...
Hey, I am using the below formula→ =INDEX({Source_Sheet_Entire_Table}, MATCH([Unique ID]@row , {Source_Sheet_ID_Column}, 0), 1) Although the answer should be "Project details" mentioned in Column 1, it says #No Match. What am I doing wrong?
I have inly one user in my trial account. When I am trying to upgrade to PRO plan it is defaulting for 2 users and not allowing me to upgrade the plan for only one user. Please help.
How can I delete old sheets I no longer use from my account?
