Counting months

[robertdseals]

Hello,
I'm trying to count the number of complete months between two dates. 1/15/23 - 5/21/23 would equal 3. The problem occurs that they also want to include months that start on a weekend. So, if someone starts on monday the 2nd, they would get credit for that entire month. Lastly, you would need to take into account holidays (US). So, if someone was hired on September 4th because the 1st was on Saturday and memorial day was on Monday they would still get credit for the entire month. But, if they were hired on the 4th of another month without a holiday, they wouldn't get credit for that month.

 

[awoohaw]

can you give a broad range of examples and expected results in a table or even a XL2BB add in (link below) mini worksheet? You're asking the forum to manually recreate your scenario and they can take time, and/or have error of assumptions or typos (which also causes more time).

Thanks in advance.

 

[Tetra201]

See if the following formula works for you:

=ROUND((TEXT(B2,"yyyy mm\/\1\2")-TEXT(A2,"yyyy mm\/\1\2"))*12,0)+(WORKDAY(A2-1,1,Holidays)=WORKDAY(EOMONTH(A2,-1),1,Holidays))+(WORKDAY(B2,1,Holidays)-1=EOMONTH(B2,0))-1

where cell A2 contains the start date, cell B2 contains the end date, and a range named Holidays contains holiday dates.

 

[JamesCanale]

MrExcelPlayground17.xlsx
	B	C	D	E
2	Start	End	Whole Months	Holidays
3	Tuesday, September 4, 2029	Friday, September 28, 2029	1	Monday, January 1, 2029
4	Wednesday, September 5, 2029	Wednesday, October 31, 2029	1	Monday, September 3, 2029
5	Tuesday, September 4, 2029	Wednesday, October 31, 2029	2	Wednesday, July 4, 2029
6	Wednesday, September 5, 2029	Wednesday, October 31, 2029	1
7	Wednesday, September 5, 2029	Tuesday, December 25, 2029	2
8	Wednesday, September 5, 2029	Monday, December 31, 2029	3
9	Wednesday, September 5, 2029	Friday, February 15, 2030	4
Sheet23

Cell Formulas
Range	Formula
D3:D9	D3	=MAX(0,IF(NETWORKDAYS(C3,EOMONTH(C3,0),$E$3:$E$8)=1,(YEAR(C3)*12+MONTH(C3)),(YEAR(C3)*12+MONTH(C3))-1)-IF(NETWORKDAYS(EOMONTH(B3,-1)+1,B3,$E$3:$E$8)=1,(YEAR(B3)*12+MONTH(B3)),(YEAR(B3)*12+MONTH(B3))+1)+1)