Count months in which an employee is active during a financial period

[Surtsey]

Dear all,

I have spent my last couple of days unsuccessfully trying to find out an Excel formula hence I am turning to this Forum's wisdom.
NOTE: I installed and tried to use XLS2BB add-in but I cannot bypass the macro-locking.

Based on the start/end dates of an employee, I need to count the number of partial months in which the employee was active during a given reporting period.
1) I have four dates in total as shown in the image below:
- Financial Period Start and End Date;
- Employee Start and End Date.

With a simple IF Formula in D6 and E6, I was able to calculate the number of complete (nor partial) months as follows:
Formula in cell D6:

=IFERROR(IF(B6<$B$2,DATEDIF($B$2,IF(C6<$C$2,C6,$C$2),"m")+1,DATEDIF(B6,IF(C6<$C$2,C6,$C$2),"m")+1),0)

Formula in cell E6:

=IFERROR(IF(B6<$B$3,DATEDIF($B$3,IF(C6<$C$3,C6,$C$3),"m")+1,DATEDIF(B6,IF(C6<$C$3,C6,$C$3),"m")+1),0)

The problem arises when the start/end dates of employee or financial reporting does not coincide with the 1st day of the month. If the example above:
* For employee 1, the "Months active within FP2" should be 11.5 instead of 12; and
* For employee 2, the "Months active within FP2" should be 10.17 instead of 11.

I hope I was able to depict my issue correctly. Thanks a lot in advance!

 

[Peter_SSs]

NOTE: I installed and tried to use XLS2BB add-in but I cannot bypass the macro-locking.

Does this help? XL2BB Icons greyed out

 

[Surtsey]

Does this help? XL2BB Icons greyed out

It helped, Peter_SSs, thank you

Below the file:

MR Excel.xlsx
	A	B	C	D	E
1	Financial Period	Start date	End date
2	FP1	01/06/2021	31/05/2022
3	FP2	01/06/2022	31/05/2023
4
5
6	Employee	Start Date	End Date	Months active within FP1	Months active within FP2
7	Janusz Cebula	01/01/2021	16/05/2023	12.0 months	12.0 months
8	James James	26/07/2021	30/06/2023	11.0 months	12.0 months
Personnel SAL_Jan-Jun'23

Cell Formulas
Range	Formula
D7:D8	D7	=IFERROR(IF(B7<$B$2,DATEDIF($B$2,IF(C7<$C$2,C7,$C$2),"m")+1,DATEDIF(B7,IF(C7<$C$2,C7,$C$2),"m")+1),0)
E7:E8	E7	=IFERROR(IF(B7<$B$3,DATEDIF($B$3,IF(C7<$C$3,C7,$C$3),"m")+1,DATEDIF(B7,IF(C7<$C$3,C7,$C$3),"m")+1),0)

The problem arises when the start/end dates of employee or financial reporting does not coincide with the 1st day of the month. If the example above:
* For employee 1, the "Months active within FP2" should be 11.5 instead of 12; and
* For employee 2, the "Months active within FP2" should be 10.17 instead of 11.

I hope I was able to depict my issue correctly. Thanks a lot in advance!

 

[levipe]

First, you can shorten your existing formulas to

=IFERROR(DATEDIF(MAX(B7,$B$2),MIN(C7,$C$2),"m")+1,0)

On your issue, this just seems to be a limitation of the DATEDIF() function, so you'll probably have to replace your existing formula with the more complicated one you used to determine what they should be (though I got 10.19 for employee 2). If your version supports it, I'd probably pursue the LET() function for this.

 

[Peter_SSs]

Not sure exactly how you want to calculate the decimal part or how accurate it needs to be, but see if this is any use.

BTW, Welcome to the MrExcel board!

Surtsey.xlsm
	A	B	C	D	E
1	Financial Period	Start date	End date
2	FP1	1/06/2021	31/05/2022
3	FP2	1/06/2022	31/05/2023
4
5
6	Employee	Start Date	End Date	Months active within FP1	Months active within FP2
7	Janusz Cebula	1/01/2021	16/05/2023	12	11.5
8	James James	26/07/2021	30/06/2023	10.16666667	12
9	abc	28/05/2023	14/06/2023	0	0.1
Sheet1

Cell Formulas
Range	Formula
D7:D9	D7	=LET(s,MAX(B$2,B7),e,MIN(C$2,C7),m,DATEDIF(s,e,"m"),d,DATEDIF(EDATE(s,m),e,"d"),IFERROR(m+d/30,0))
E7:E9	E7	=LET(s,MAX(B$3,B7),e,MIN(C$3,C7),m,DATEDIF(s,e,"m"),d,DATEDIF(EDATE(s,m),e,"d"),IFERROR(m+d/30,0))

 

[Surtsey]

Not sure exactly how you want to calculate the decimal part or how accurate it needs to be, but see if this is any use.

BTW, Welcome to the MrExcel board!

Surtsey.xlsm
	A	B	C	D	E
1	Financial Period	Start date	End date
2	FP1	1/06/2021	31/05/2022
3	FP2	1/06/2022	31/05/2023
4
5
6	Employee	Start Date	End Date	Months active within FP1	Months active within FP2
7	Janusz Cebula	1/01/2021	16/05/2023	12	11.5
8	James James	26/07/2021	30/06/2023	10.16666667	12
9	abc	28/05/2023	14/06/2023	0	0.1
Sheet1

Cell Formulas
Range	Formula
D7:D9	D7	=LET(s,MAX(B$2,B7),e,MIN(C$2,C7),m,DATEDIF(s,e,"m"),d,DATEDIF(EDATE(s,m),e,"d"),IFERROR(m+d/30,0))
E7:E9	E7	=LET(s,MAX(B$3,B7),e,MIN(C$3,C7),m,DATEDIF(s,e,"m"),d,DATEDIF(EDATE(s,m),e,"d"),IFERROR(m+d/30,0))

Hi Peter_SSs,

1) Thanks a lot for the welcome!
2) The LET formula you have produced is absolutely PERFECT - thanks a million to you (and to levipe) for the great help. I owe you a coffee (to say the least).

 

[Peter_SSs]

You're welcome. Glad we were able to help. Thanks for the follow-up.