Formula on calculating month allocation per resource
So I am trying to find the most accurate way to write a formula that will calculate monthly allocation per resource. I am aware Smartsheet has Resource Management add-on but my company is not interested in purchasing that for the time being.
Instead, what I am trying to do is come up with a formula that calculates months based on start and end date and then the allocation I have divide it by the number of months. What I have so far is not really accurate because for 5% allocation, and a 2 month timeline the system is giving me 1 month. (see screenshot below - start date is beginning of April and end date is end of May - I want the month to be counted as 2 not 1 since there is definitely more days we're working on this task) Any thoughts?
I tried =([Target End Date]@row - [Target Start Date]@row) / 30
then I tried =([Target End Date]@row - [Target Start Date]@row) / 30.425 since I read this formula in another post here but not helping.
Lastly, I tried =MONTH([Target End Date]@row) - MONTH([Target Start Date]@row) which is what I'm using below but all of these are not as accurate as I want them to be.
Thanks
Best Answer
-
Try
MONTH(.....) - MONTH(.....) + 1
Answers
-
Try
MONTH(.....) - MONTH(.....) + 1
-
Thanks for the reply, I was able to use it for the majority of my rows but I have some projects that have a start date in 2023 and end in 2024 but the function is giving me a negative number when counting the months. Any idea how to fix this?
see below
-
For that we would need something like this:
(MONTH(End) - MONTH(Start) + 1) + (12 * (YEAR(End) - YEAR(Start)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!