Count of Days per month for a contract period

[sspilla]

Hi All -

I have a list of employees and their billing period against each contract.
I need to calculate the days billed for each employee against each contract by month for last 12 months.
My worksheet is as below:

My expected result is as below:

Thanks in advance.

Regards,
SSP

 

[fadee2]

hi sspilla,
Welcome to the board.

Pasting screenshots of the data does not provide actual data to work with. I would suggest providing sample data using xl2bb addon. This way it would be much easier for everyone trying to help, to work with your query and suggest solution.

 

[sspilla]

Hi All -

20201214_Deployed Emp in FT_FT Utilization_WIP.xlsx
	A	B	C	D	E	F	G	H
1	Employee Id	Employee Name	Employee Substatus Name	Contract no	Start date	End date	Deployed- Jan'20	Deployed- Feb'20
2	491	HALIMA MUHAMMED	Under billing	IND-FT-009870	2/12/2020	3/12/2020	No	17
3	793	RAM LAKHAN	Under billing	IND-FT-007963	12/15/2019	2/23/2020	31	23
4	800	SHEIKH MATIN	Under billing	IND-FT-008939	1/6/2020	4/4/2020	25	29
5	800	SHEIKH MATIN	Under billing	IND-FT-011110	4/5/2020	7/3/2020	No	No
6	1084	FREWIENI MEDHN TESFAHANES	Under billing	IND-FT-007005	9/26/2019	6/30/2020	31	29
7	1086	YRGALEM GHEBREYESUS MATIWOS	Under billing	IND-FT-000451	10/22/2019	9/4/2020	31	29
8	1088	SENAYIT WELDEMIKAEL GEBREAB	Under billing	IND-FT-008224	12/30/2019	1/23/2020	No	No
9	1088	SENAYIT WELDEMIKAEL GEBREAB	Under billing	IND-FT-002743	1/30/2020	2/1/2020	2	1
10	1088	SENAYIT WELDEMIKAEL GEBREAB	Under billing	IND-FT-009719	2/6/2020	9/30/2020	No	23
11	1089	SHEWIT GEBREZGABIHER ARAYA	Under billing	IND-FT-002809	1/13/2020	9/3/2020	18	29
12	1090	TEGSTI TESFAY SEBHATU	Under billing	IND-FT-001781	12/7/2018	1/1/2020	No	No
13	1095	SENAIT KFLEMARIAM MESGNA	Under billing	IND-FT-004550	1/17/2020	2/15/2020	14	15
14	1095	SENAIT KFLEMARIAM MESGNA	Under billing	IND-FT-011088	3/17/2020	4/8/2020	No	No
15	1753	YODIT OQBATSION UQBAMIKAEL	Under billing	IND-FT-011174	4/26/2020	6/25/2020	No	No
16	1756	ALEM UQBAY ABRAHALEY	Under billing	IND-FT-011088	4/9/2020	4/14/2020	No	No
Sheet5

 

[sspilla]

hi sspilla,
Welcome to the board.

Pasting screenshots of the data does not provide actual data to work with. I would suggest providing sample data using xl2bb addon. This way it would be much easier for everyone trying to help, to work with your query and suggest solution.

Thanks for the pointer. I have given the data using xl2bb addon

 

[fadee2]

Thanks for the response.
please explain, what to make of "NO" in columns G and H.

 

[sspilla]

Thanks for the response.
please explain, what to make of "NO" in columns G and H.

In Cell G2, the start date is Feb-12,2020. Hence the deployment in Jan 2020 is No and in Feb (Cell H2) there will be 17 days.

 

[kvsrinivasamurthy]

Make these changes. Enter dates.
G1=1 Jan 2020
H1 =1 Feb 2020
In G2 then copied across

=MAX(0,MIN(EOMONTH(G$1,0),$F2)-MAX(G$1,$E2)+1)