Checking what Months are during a Project
Hi,
I've tried to find a solution for this, but maybe it's a too generic wording problem.
We have a Master Project list, that has the start and end date of every project we have, as well as it's budget and projected profits.
I want to have an overview of how much money is allocated to each month and for that I need to check how many and which months are within the project time and have the budget divided by the number of months and allocated to each month.
I've tried to solve it by creating a column for every month of the year and make it a checkbox with the following formula (for January):
=IF(OR(MONTH([Startdate]1) = 1; MONTH([Enddate]1) = 1); 1; IF(AND(MONTH([Startdate]1) < 1; MONTH([Enddate]1) > 1); 1; IF(YEAR([Startdate]1) < YEAR([Enddate]1); 1; 0)))
This checks every month, but has issues as soon as it's across years, because it's always true.
Question is, what would be a solution that covers all project lengths, including those across years?
Or is there maybe another, easier way to solve the whole problem of trying to allocate to each month within a time period?
Thank You in Advance!
Comments
-
Hi Thomas -
I used the PRORATE formula to solve a very similar situation. I was hoping to be able to quickly perform CASH FLOW Projections per month by calculating anticipated revenue, Anticipated Expense and GP margin each month. The trouble was that when scheduled dates changed other financial projections were troublesome to update and adjust. NOW when the project changes START AND END DATES Smartsheet re-calculates the prorated amounts for each period instantly & automatically.
I have attached a couple screenshots of a sheet another user posted that helped me understand PRORATE and how to implement in order to achieve financial projections for changing projects.
The below formula was used in the screenshot examples attached to determine proration per year:
=PRORATE($[Full € quote]4, $Start4, $Finish4, DATE([2015 € expenses]$1, 1, 1), DATE([2015 € expenses]$1, 12, 31))
Additional Information on PRORATE Function can be found at the link below:
https://help.smartsheet.com/function/prorate
& also I recently learned that the PRORATE function calculates on elasped time (Calendar Days) NOT working days in the schedule. This didn't make a difference in what I was doing but thought I'd mention in case it makes a difference.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!