Calculate % complete with Date Range and Today's date

Sam Swain
Sam Swain ✭✭
inFormulas and Functions

Hello community,

I need a formula to return % Complete based on a start - end date range and today's date.

So for example:

Date Range: 06/18/23 - 06/30/23

Today's date: 06/26/23

As there are 12days in this date range, and today's date is day 8 out of 12, the percentage complete would be 66.6%

Thanks in advance!

Tags:
· Share on Facebook Share on Twitter

Best Answer

Answers

  • Sam Swain
    Sam Swain ✭✭

    Follow up - this is my solution but maybe there is a single formula option?

    Currently I have two helper columns

    Days:=IFERROR(NETDAYS([email protected],[email protected]), "")

    Today - Start:=IF([email protected]> TODAY(), TODAY() -[email protected])

    % Complete:=IFERROR([Today - Start]@row /[email protected], "")

    Screenshot 2023-06-26 at 12.54.11.png


    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:

    =MAX(MIN((TODAY() -[email protected]) / ([email protected]-[email protected]), 1), 0)

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Sam Swain
    Sam Swain ✭✭
    Try this:<\/p>

    =MAX(MIN((TODAY() - Start@row) \/ (Finish@row - Start@row), 1), 0)<\/p>","bodyRaw":"[{\"insert\":\"Try this:\\n=MAX(MIN((TODAY() - Start@row) \\\/ (Finish@row - Start@row), 1), 0)\\n\"}]","format":"rich","dateInserted":"2023-06-26T12:00:48+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T17:08:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382253#Comment_382253","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/382253#Comment_382253

    That worked, thanks again Paul!

    It also shows 100% complete when it is in the past which is great. What part of this formula makes it do that?

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    =MAX(MIN((TODAY() -[email protected]) / ([email protected]-[email protected]), 1), 0)


    This does the percentage:

    (TODAY() -[email protected]) / ([email protected]-[email protected])


    This caps it at 100%:

    MIN(..............., 1)


    This keeps it from going negative for future tasks:

    MAX(..............., 0)

    thinkspi.com

    · Share on Facebook Share on Twitter
  • Sam Swain
    Sam Swain ✭✭
    =MAX(MIN((TODAY() - Start@row) \/ (Finish@row - Start@row), 1), 0)<\/p>

    This does the percentage:<\/p>

    (TODAY() - Start@row) \/ (Finish@row - Start@row)<\/p>

    This caps it at 100%:<\/p>

    MIN(..............., 1)<\/p>

    This keeps it from going negative for future tasks:<\/p>

    MAX(..............., 0)<\/p>","bodyRaw":"[{\"insert\":\"=MAX(MIN((TODAY() - Start@row) \\\/ (Finish@row - Start@row), 1), 0)\\n\\nThis does the percentage:\\n(TODAY() - Start@row) \\\/ (Finish@row - Start@row)\\n\\nThis caps it at 100%:\\nMIN(..............., 1)\\n\\nThis keeps it from going negative for future tasks:\\nMAX(..............., 0)\\n\"}]","format":"rich","dateInserted":"2023-06-26T13:24:44+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T17:08:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382268#Comment_382268","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/382268#Comment_382268

    Thanks Paul, very helpful as always!

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.️

    thinkspi.com

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
@v.winters<\/a> <\/p>

If the two percent columns are formatted as percent columns (see screenshot), then the % Not Complete column would have this formula: <\/p>

=1 - [%Complete]<\/em>@row<\/p>

\n
\n \n \"Menu.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107009,"type":"question","name":"I need help with a if formula to get the $ amount total if another column = a specific item.","excerpt":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","snippet":"I want to make a formula in the Column \"A Total\" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.","categoryID":322,"dateInserted":"2023-06-28T12:55:48+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T16:37:28+00:00","insertUserID":162272,"insertUser":{"userID":162272,"name":"Elayne Smith","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Elayne%20Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z_neMx7Hvj4!mVDfnuqtMWQ!RH9Ly5uQ-po","dateLastActive":"2023-06-28T13:41:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T17:08:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3375929596,"url":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107009\/i-need-help-with-a-if-formula-to-get-the-amount-total-if-another-column-a-specific-item","format":"Rich","lastPost":{"discussionID":107009,"commentID":382888,"name":"Re: I need help with a if formula to get the $ amount total if another column = a specific item.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382888#Comment_382888","dateInserted":"2023-06-28T16:37:28+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T17:08:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/N80WFJ95P6OH\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T13:45:50+00:00","dateAnswered":"2023-06-28T13:05:16+00:00","acceptedAnswers":[{"commentID":382818,"body":"

Hi @Elayne Smith<\/a>,<\/p>

The formula for this would be:<\/p>

=SUMIF(Project:Project, \"A\", [Grand Total]:[Grand Total])<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - any issues etc. then just post! 😊<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107018,"type":"question","name":"ROI Calculation Formula","excerpt":"I have a sheet that gathers numbers for various project costs and for various values. There are two columns that sum each of these; Total Costs and Total Value. I am using the following formula to calculate the ROI: =([Total Value]@row - [Total Costs]@row) \/ [Total Costs]@row * 100 However, the calculation seems to be off.…","snippet":"I have a sheet that gathers numbers for various project costs and for various values. There are two columns that sum each of these; Total Costs and Total Value. I am using the…","categoryID":322,"dateInserted":"2023-06-28T14:24:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T15:14:39+00:00","insertUserID":159640,"insertUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":159640,"lastUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3375928741,"url":"https:\/\/community.smartsheet.com\/discussion\/107018\/roi-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107018\/roi-calculation-formula","format":"Rich","lastPost":{"discussionID":107018,"commentID":382858,"name":"Re: ROI Calculation Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382858#Comment_382858","dateInserted":"2023-06-28T15:14:39+00:00","insertUserID":159640,"insertUser":{"userID":159640,"name":"Katherine Simpson","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Simpson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/EY1XYADLUP90\/n8693BY7J2F39.jpg","dateLastActive":"2023-06-28T15:13:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T15:14:06+00:00","dateAnswered":"2023-06-28T14:56:38+00:00","acceptedAnswers":[{"commentID":382852,"body":"

Try omitting the \"*100\" at the end of your formula. Convert the column to show the number as a percentage. <\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • How to subtract percentages?

    I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will populate an answer, however I cannot adjust the formatting from % to number Example: %Complete = 13% Correct answer should display 87% Second…

  • I need help with a if formula to get the $ amount total if another column = a specific item.

    I want to make a formula in the Column "A Total" in the highlighted Cell, that will give me the Sum of $ from Grand Total Column if the A is in the Project column.

  • ROI Calculation Formula

    I have a sheet that gathers numbers for various project costs and for various values. There are two columns that sum each of these; Total Costs and Total Value. I am using the following formula to calculate the ROI: =([Total Value]@row - [Total Costs]@row) / [Total Costs]@row * 100 However, the calculation seems to be off.…