Formula for Current and Past Projects

Tony Fronza

Hello! We're developing a master project index sheet, dashboard, and reports. We want to showcase summary data on the dashboard for the total # of projects in the current year, upcoming year, and past (any date before the current year) based on the project's Start Date and if the Est. Completion Date (i.e., if it's started in 2022 but won't be completed until 2023, we want the project to be counted for both years). Is there a formula to show this? We'd prefer to use column formulas versus summary data (i.e., create a field for 2022 total, 2023 total, etc.).

Our headers are as follows:

* Project Name

* Est. Completion Date

* Completion Date

* Year

We will likely want to show projects completed by date at some point, too.

Any help is greatly appreciated!

Samuel Mueller

@Tony Fronza

May need a helper column to extract year from your two columns. Like ProjectYearHelper, and then the formula would be something like

=YEAR([start date]@row) + "," + YEAR([Est. Completion Date]@row)

Then you could do a countifs over the range to get number of projects contained in that year

=COUNTIFS(ProjectYearHelper:ProjectYearHelper, CONTAINS("2022", @cell))

Let me know if this helps, or if I misunderstood your question.

Tony Fronza

Thanks, @Samuel Mueller! I'm going to add a helper column and try this out shortly.

Tony Fronza

@Samuel Mueller is there a way to modify the COUNTIFS to avoid the need to enter a specific year? For example, change the CONTAINS to a command that looks for current, future, or past years.

=COUNTIFS(ProjectYearHelper:ProjectYearHelper, CONTAINS("2022", @cell))

Samuel Mueller

You could probably use year(today()) in place of "2022"

Then you could add plus 1 or minus one to that

Tony Fronza

This works for now! Thanks, Sam.