Filter by date to determine days a task has in each month

1

HI All - I am creating a project plan where each task gets assigned a start date, end date, and resource. I would like to determine what resources are needed each month. Generally straight forward until a task crosses months. Does anyone know of a formula that can determine number of days a task has in each month e.g. Design Plan 1/20/19 - 2/2/19 11 days in Jan and 2 in Feb?

Thanks!

3

Unfortunately there isn’t a straightforward solution to this problem. but here’s a document that I put together that could help

Tasks Data:

TaskAllocation2
TaskAllocation2.png1798×854 52.4 KB

Resource Allocation:

Task%20Allocation1
Task Allocation1.png2466×874 86.5 KB

Here’s how it works:

  1. Tasks table has column Sequence([Start Date].ToNumber(), [End Date].ToNumber(), 1).FormulaMap(Year(CurrentValue)+ ":" + Month(CurrentValue)).Unique() should give you unique months between Start Date and End Date

  2. There is a master table of years - which has button that populates Month rows in Resource Allocation For Month for that year. The very same table has formula Tasks.Filter([Unique Months].Contains(thisRow.MonthName)).Resources.Sum() - that gives resources for that month.

note:

  • does not work if you have non-overlapping tasks in same month and you could technically reuse resources for month. for e.g, if first part of the month has 10 resources and second half has another month with 5 resources - I would think you could reuse resources from first Task into second. but this would give you 15 as a requirement (if this doesnt work for your needs - you could do unique days instead of unique months I am doing in above document)

Hope this helps!

Thank you,
Krunal.

2 Likes
4

Thanks!!! Super helpful