Percentage of the Year Formula
Good Afternoon!
I'm currently constructing some calculations and I would like some help with a formula that will show the "percentage of year" that a goal will achieve if completed by it's due date.
Example:
- Project Due Date = 6/1/21
If the project is completed on time (due date), then that project will have achieved goal attainment for 50% of the year (6 months of being completed for the remaining year).
Any help in creating a formula for this would be greatly appreciated!
Thanks,
Luke
Best Answer
-
I was able to figure it out based on the input you gave Steven, thank you very much!
The correct formula ended up being:
=(DATE(YEAR([Project ETBR]@row), 12, 31) - [Project ETBR]@row) / 365
If project due date was 1/1/21 then above formula would show 100%.
If project due date was 12/31/21 then above formula would show 0%.
Regards,
Luke
Answers
-
Hi Luke,
Assuming your project due dates are in a column named "Project Due Dates" and the the formula you are writing is in the same row as the dates, you can use the following formula to calculate the fraction:
=([Project Due Date]@row - DATE(YEAR([Project Due Date]@row), 1, 1)) / 365
Then format the column/cell with the formula as percentage.
-
Hi Steven,
Thanks for helping out with this!
That worked to a point, but I think the percentage calculation is a bit off.
For example, the formula provided gives me a result of 0% with a Project Due Date of 1/1/21, when it should be 100%, since it will have been completed for 100% of the year .
Does that make any sense?
-
I was able to figure it out based on the input you gave Steven, thank you very much!
The correct formula ended up being:
=(DATE(YEAR([Project ETBR]@row), 12, 31) - [Project ETBR]@row) / 365
If project due date was 1/1/21 then above formula would show 100%.
If project due date was 12/31/21 then above formula would show 0%.
Regards,
Luke
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!