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...
I have a grid with two forms: one in English and one in German. The dropdown questions are working fine, but the answer options always appear in English. When I try to change the options to German, Smartsheet automatically updates the answers in the English form as well. I’ve tested this several times, and every time I…
I had data deleted from a column in a tracking sheet. (2K+ rows) I pulled a snapshot from the activity log and I want to pull back the data from just the one column. I was told the right process was under File>Import>Update Rows, but I don't have that option under import. Is there another way to do this?
I am receiving errorCode: 48 on one of my sheets that is in a Workspace. All other sheets can be accessed except for the most important one. I have created 2 cases for this with no response from Smartsheet: Case# 08756481 Case# 08759594
