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...
Here is a simple Smartsheet automation - When rows are added and 'Status' is blank, change the status to 'Open'. It does not trigger on addition of a row where 'Status' is blank. If I run it manually it works. If I select any other value instead of blank, it triggers and works. I have been struggling with this automation…
Our office is analyzing if Smartsheet will work on a new task we've been assigned. We would like to 1) send a Smartsheet to about 250 users, each user fills in a variety of data (some will have 3 rows of information and others will have about 200 rows of information), and then 2) later combine those responses into one…
I work in real estate and I want to build a custom dashboard/template to track where we are at with deals. The pre-made templates are all too specific and hard to use. I've created my own sheet/intake form but am looking for support on building the dashboard and how information is shown.
