Search code examples
excelfunctiondateexcel-formulaproject-management

Occurrence by month using Excel Functions

I am working on summarizing the amount of items between two dates in excel using functions. Sometimes there is no end date so the today function could serve as showing the item is still open. If its possible to further break out by the year that would be great. Sometimes items reopen and this is included in the example but they should be counted as individual items. If its possible to toggle between years that would help even more to compare the volume.

Data:

Start Date End Date Progress Category Start Date Month and Year End Date Month and Year
4/19/2023 11/1/2023 Complete 4-2023 11-2023
4/3/2023 1/2/2024 Complete 4-2023 1-2024
1/4/2024 4/2/2024 Complete 1-2024 4-2024
5/17/2024 6/4/2024 Complete 5-2024 6-2024
5/17/2024 7/30/2024 Complete 5-2024 7-2024
9/4/2024 12/3/2024 Complete 9-2024 12-2024
1/3/2025 <100% 1-2025 1-1900
1/4/2024 4/2/2024 Complete 1-2024 4-2024
5/17/2024 7/30/2024 Complete 5-2024 7-2024
1/3/2025 <100% 1-2025 1-1900
4/2/2024 10/29/2024 Complete 4-2024 10-2024
2/1/2024 12/3/2024 Complete 2-2024 12-2024
12/9/2024 <100% 12-2024 1-1900
4/3/2024 <100% 4-2024 1-1900
7/27/2023 1/2/2024 Complete 7-2023 1-2024
7/27/2023 6/4/2024 Complete 7-2023 6-2024
7/27/2023 9/3/2024 Complete 7-2023 9-2024
7/27/2023 1/2/2024 Complete 7-2023 1-2024
4/5/2024 10/29/2024 Complete 4-2024 10-2024
4/2/2024 12/31/2024 Complete 4-2024 12-2024
6/4/2024 12/3/2024 Complete 6-2024 12-2024

Expecting: Expecting

I tried using datediff function and sumproduct function but i would only get the sum of the months between the two dates.

Interesting, I know. Thank you!

Solution

  • Without your data, I created dummy data to explain the concept of how to use a COUNTIFS formula to achieve your goal. You asked to count all the Progress categories for the dates between the dates listed.

    First you want the month numbers for your month labels, so I added 1-12 above those we'll use for comparison. Then I added another column to your table to apply the logic you explained, where if there is no end date I use today's date using the today() formula.

    Lastly, I use a countifs to count how many "Complete" or "< 100%" there is for every date >= the start date, and <= the end date. I use the date function to construct the first date of the month and the end of the month so that it doesn't matter when the date appears in the month it will use the entire month's dates.

    These are the formulas I used adjust as needed:

    =IF(ISBLANK(B2),TODAY(),B2)

    =COUNTIFS($C$2:$C$8,$G3,$A$2:$A$8,"<=" & DATE($G$2,H$1,1),$D$2:$D$8,">="& EOMONTH(DATE($G$2,H$1,1),0))

    Image1