How To Collect Data based on date and post to proper month column

PattiL

Hi All!

I have a several large projects running in a portfolio. The projects are all construction in nature and with each project there are subsections of construction each with target completion footage and target completion dates.

I would like to build a report to show me the total build footage across the various projects by month based on the project dates (task end dates).

Project #1:

Build activity #1 1/1/22 - 3/1/22 100 feet

Build activity #2 3/2/22 - 4/1/22 200 feet

Project #2:

Build activity #1 1/1/22 - 3/1/22 50 feet

Build activity #2 3/2/22 - 4/15/22 75 feet

Desired Reporting:

Is there a formula that i can use to reference the task completion date of say 3/1/22 and would thus put the 100 in the Mar column? Then if those dates get changed to day 6/8/22, auto update the above example and show the 100 items in the Jun column?

Thanks in advance for any guidance! I struggle with formulas.

ericncarr

Hi @PattiL ,

If your organization has Datamesh I'd recommend creating a report that gathers all the tasks from the various projects into one report, then Datamesh that into a sheet (on an hourly or daily basis). That way you can do a formula that will always stay the same and you won't have to create new references whenever new projects start.

Whether you do it that way or not, the base formula would be something like this, using January as an example:

=sumifs({Square Footage reference}, {Completion Date reference}, >= date(year(today()), 1, 1), {Completion Date reference}, < date(year(Today()), 2, 1))

For December, just tweak the formula slightly:

=sumifs({Square Footage reference}, {Completion Date reference}, >= date(year(today()), 1, 1), {Completion Date reference}, < date(year(Today()) +1, 2, 1))

The formula will do a sumifs on the square footage column using the completion date as the criterion twice, once to check if the date is greater than or equal to the 1st of January of the current year and the second to check if it's less than the 1st of the following month. In the December formula you're just giving the following month an additional year (1/1/2023 instead of 2022...).

ericncarr

Hi @PattiL ,

If your organization has Datamesh I'd recommend creating a report that gathers all the tasks from the various projects into one report, then Datamesh that into a sheet (on an hourly or daily basis). That way you can do a formula that will always stay the same and you won't have to create new references whenever new projects start.

Whether you do it that way or not, the base formula would be something like this, using January as an example:

=sumifs({Square Footage reference}, {Completion Date reference}, >= date(year(today()), 1, 1), {Completion Date reference}, < date(year(Today()), 2, 1))

For December, just tweak the formula slightly:

=sumifs({Square Footage reference}, {Completion Date reference}, >= date(year(today()), 1, 1), {Completion Date reference}, < date(year(Today()) +1, 2, 1))

The formula will do a sumifs on the square footage column using the completion date as the criterion twice, once to check if the date is greater than or equal to the 1st of January of the current year and the second to check if it's less than the 1st of the following month. In the December formula you're just giving the following month an additional year (1/1/2023 instead of 2022...).

PattiL

Thank you, Erin! I will give this a try. Patti

ericncarr

@PattiL happy to help, hope it works out well!