Formula to count months

[Glacombe]

Hello
I have the following formula that counts how many months worked in a fiscal year and not counting holidays and also if worked 10 or more days in the month it will count that month. The formula verifies the start date which is in AJ10 and the end date which is in AK10.

=IF(AND(ISBLANK(AJ10),ISBLANK(AK10)),0,(YEAR(AK10)-YEAR(AJ10))*12+MONTH(AK10)-MONTH(AJ10)-1+IF(NETWORKDAYS.INTL(AJ10,EOMONTH(AJ10,0))>=10,1,0)+IF(NETWORKDAYS.INTL(EOMONTH(AK10,-1),AK10,1)>=10,1,0))

What I am trying to revise this formula is to count if worked half the month to count it. For example if the period would be April 1, 2020 to September 15, 2020, I would like the result of the formula to show 5.5 months instead 6.

 

[StephenCrump]

Currently you're defining a month as 10 working days in any calendar month. So a period as short as 17 April to 14 September 2020 counts as two months.

If you make the change, how will you define a month? Half a month?

 

[Glacombe]

The result of my formula counts if equals or more than 10 working days, excluding weekend, it counts as a full month and the result of the above start date and end date is 6 months. As the person worked 10 work days in April, therefore counts the full month and that person worked more than 10 work days in September, therefore, counts the full month of September.
I would like to revise that formula to count only half of the month if working 14 days in that month, therefore the above start and end date would be 5.5 months.

 

[JamesCanale]

I left this in many cells to make it easier to follow. You can smash it all into one cell if you please.

MrExcelPlayground.xlsm
	I	J
10	4/20/2021	1/22/2022
11
12	Full month > X days	14
13	half month >= X days	5
14	no month < X days	5
15
16	whole months	8
17	Start days	9
18	End days	15
19	start months	0.5
20	end months	1
21	Total Months	9.5
Sheet16

Cell Formulas
Range	Formula
J16	J16	=(YEAR(J10)*12+MONTH(J10))-(YEAR(I10)*12+MONTH(I10))-1
J17	J17	=NETWORKDAYS.INTL(I10,EOMONTH(I10,0),1)
J18	J18	=NETWORKDAYS.INTL(DATE(YEAR(J10),MONTH(J10),1),J10,1)
J19	J19	=IF(J17>J12,1,IF(J17>=J13,0.5,0))
J20	J20	=IF(J18>J12,1,IF(J18>=J13,0.5,0))
J21	J21	=J16+J19+J20

I assumed that if you work less than 5 days, it's 0 months. If it's more than 14 it's one month, between it's half. You can change that as you please.

 

[Glacombe]

Sorry my start date and end date are on different columns and would like to count half the month if working 14 days or more

 

[JamesCanale]

How about:

MrExcelPlayground.xlsm
	AJ	AK	AL
10	4/20/2021	1/22/2022	8.5
Sheet16

Cell Formulas
Range	Formula
AL10	AL10	=IF(OR(AJ10="",AK10=""),"",((YEAR(AK10)*12+MONTH(AK10))-(YEAR(AJ10)*12+MONTH(AJ10))-1)+IF(NETWORKDAYS.INTL(AJ10,EOMONTH(AJ10,0),1)>=14,0.5,0)+IF(NETWORKDAYS.INTL(DATE(YEAR(AK10),MONTH(AK10),1),AK10,1)>=14,0.5,0))

 

[Glacombe]

Yes, it worked thank you very much James

 

[Glacombe]

Sorry James I was too quick it worked for the dates above but when I have the start date April 1, 2020 and end date December 1, 2020, the result shows 7.5 when it should be 8 months

 

[Glacombe]

I also have the formula looking if there are not dates in either column to leave the cell blank

 

[JamesCanale]

What the formula is doing is counting the full months between the dates first, so April through December counts May, June, July,..November (7 months), then adds 0 or .5 months for the edge months. That's why I had >14 is a month, between 5 and 14 is half month, less than 5 was 0 months. I could make it > 19 workdays is a month, >=14 is half, less than 14 is 0. For example, what if it was April 2- Dec 1? Would that be a month? What would make a full month?