How To Collect Data based on date and post to proper month column
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.
Best Answer
-
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...).
Answers
-
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...).
-
Thank you, Erin! I will give this a try. Patti
-
@PattiL happy to help, hope it works out well!
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!