Checking what Months are during a Project
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!
Help Article Resources
Check out the Formula Handbook template!