The number of days per month that fall between two dates

[helpExcelme]

Hello all:

I want to track and chart the total number of days per month several pieces of equipment are out of service.

The insert is just a idea on how I may go about this, it may be inefficient or even not doable, so its not set in stone.

I have a start date and an end date that each piece of equipment is out of service each time. I need a formula or a way that will extract the number of days between the start and end date for a given Month. There is overlap, so for instance line 25 May-23 =21days OOS and June-23 = 5 days OOS for a total of 26 days for that line item.

 

[JamesCanale]

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start	End	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
2	5/8/2023	5/10/2023					3
3	5/8/2023	5/10/2023					3
4	5/10/2023	6/5/2023					22	5
5	5/8/2023	5/8/2023					1
6	5/17/2023	5/17/2023					1
Sheet5

Cell Formulas
Range	Formula
D1:N1	D1	=EDATE(C1,1)
C2:N6	C2	=MAX(0,1+MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1))

 

[helpExcelme]

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start	End	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
2	5/8/2023	5/10/2023					3
3	5/8/2023	5/10/2023					3
4	5/10/2023	6/5/2023					22	5
5	5/8/2023	5/8/2023					1
6	5/17/2023	5/17/2023					1
Sheet5

Cell Formulas
Range	Formula
D1:N1	D1	=EDATE(C1,1)
C2:N6	C2	=MAX(0,1+MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1))

That worked. Thank-you!!!