Determine total numbers of days worked in a given month/year from a table of date ranges

[jrob72684]

I am looking for help in writing a formula that will query a table and return a sum of all dates worked within a specified month/year. My data has date ranges given with a start date in column A and an end date in column B. Many of the dates cross over multiple months. So far I have been able to successfully return the dates worked when looking at one specific row but I am drawing a blank on how to get excel to look at the whole table and sum the values.

The formula I used to return days worked within a given month/year for one specified row is:
=MAX(0,1+MIN($B2,EOMONTH(E$1&$D2,0))-MAX($A2,EOMONTH(E$1&$D2,-1)+1))

E1 is a cell with a label for January and D2 is a cell with a given year. This formula was then drug across to P2 to test if it worked for the first row of data across all months.

I need a formula that would return this same value but check the entire table's contents and sum total days.

Appreciate any help you can give.

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	From	To			January	February	March	April	May	June	July	August	September	October	November	December
2	11/7/2007	11/10/2007		2007	0	0	0	0	0	0	0	0	0	0	4	0
3	3/29/2008	4/5/2008		2008	0	0	3	5	0	23	26	27	9	0	0	0
4	6/8/2008	7/26/2008		2009	0	0	0	0	20	0	20	31	30	31	30	31
5	8/5/2008	9/3/2008		2010	24	0	0	2	31	30	31	6	24	31	30	31
6	9/25/2008	9/30/2008		2011	22	0	0	0	0	5	18	0	0	0	0	0
7	10/1/2008	1/23/2008		2012	17	0	0	28	0	0	0	0	0	0	0	0
8	5/6/2009	5/25/2009		2013	0	0	0	0	0	0	0	0	0	0	0	0
9	7/12/2009	9/30/2009		2014	0	0	0	0	0	0	0	0	0	0	0	0
10	10/1/2009	1/24/2010		2015	0	0	0	0	0	0	0	0	0	0	0	0
11	4/29/2010	8/6/2010		2016	0	0	0	0	0	0	0	0	0	0	0	0
12	9/7/2010	9/30/2010		2017	0	0	0	0	0	0	0	0	0	0	0	0
13	10/1/2010	1/22/2011		2018	0	0	0	0	0	0	0	0	0	0	0	0
14	6/6/2011	6/10/2011		2019	0	0	0	0	0	0	0	0	0	0	0	0
15	7/8/2011	7/25/2011		2020	0	0	0	0	0	0	0	0	0	0	0	0
16	1/2/2012	1/8/2012		2021	0	0	0	0	0	0	0	0	0	0	0	0
17	1/14/2012	1/23/2012		2022	0	0	0	0	0	0	0	0	0	0	0	0
18	3/19/2021	3/19/2012		2023	0	0	0	0	0	0	0	0	0	0	0	0
19	4/1/2012	4/28/2012		2024	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
E2:P19	E2	=LET(e,EOMONTH(E$1&$D2,0),s,EOMONTH(E$1&$D2,-1)+1,sd,$A$2:$A$19,ed,$B$2:$B$19,a,IF(ed<e,ed,e)+1,b,IF(sd>s,sd,s),SUM(IF(a-b>0,a-b)))

 

[jrob72684]

That worked perfectly! Thanks for the quick response.

 

[Eric W]

Happy to help!