Checking what Months are during a Project

Thomas Feith

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!

 

Trevor Nuccio

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.