CODEWITHSUNDEEP
excelfunctiondateexcel-formulaproject-management
Jmar
Jmar

Reputation: 21

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!

Upvotes: -3

Views: 113

Answers (2)

P.b
P.b

Reputation: 11415

Using latest Microsoft 365 you could use:

=LET(e,EOMONTH,
     m,MIN(A2:A22),
     s,SEQUENCE(,DATEDIF(e(m,0),e(TODAY(),0),"m")+1,0),
     z,EDATE(e(m,0),s),
     i,LAMBDA(j,TOCOL(IFS((z>=e(+A2:A22,0))*(z<=e(+IF(B2:B22="",TODAY(),B2:B22),0)),j),2)),
PIVOTBY(i(C2:C22),i(e(m,s)),i(s),ROWS,0,0,,0))

It starts by getting a horizontal sequence of months (from smallest date up to today).

Next with i we check if the start date is smaller or equal to and the end date is greater than equal to these months and create a 2d boolean-array out if this. If it does, return the input j, else it returns an error. If we use TO OK on the result of i we flatten the results.

We can now use this inside PIVOTBY to get the results per month (format the header row as "mm-yyyy")

Upvotes: 2

Mark S.
Mark S.

Reputation: 2584

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

Upvotes: 2

Related Questions