# Formula to count months

#### Glacombe

##### Board Regular
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.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### StephenCrump

##### MrExcel MVP
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

##### Board Regular
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

##### Active Member
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
IJ
104/20/20211/22/2022
11
12Full month > X days14
13half month >= X days5
14no month < X days5
15
16whole months8
17Start days9
18End days15
19start months0.5
20end months1
21Total Months9.5
Sheet16
Cell Formulas
RangeFormula
J16J16=(YEAR(J10)*12+MONTH(J10))-(YEAR(I10)*12+MONTH(I10))-1
J17J17=NETWORKDAYS.INTL(I10,EOMONTH(I10,0),1)
J18J18=NETWORKDAYS.INTL(DATE(YEAR(J10),MONTH(J10),1),J10,1)
J19J19=IF(J17>J12,1,IF(J17>=J13,0.5,0))
J20J20=IF(J18>J12,1,IF(J18>=J13,0.5,0))
J21J21=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

##### Board Regular

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

##### Active Member
MrExcelPlayground.xlsm
AJAKAL
104/20/20211/22/20228.5
Sheet16
Cell Formulas
RangeFormula
AL10AL10=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

##### Board Regular

Yes, it worked thank you very much James

#### Glacombe

##### Board Regular
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

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

#### JamesCanale

##### Active Member
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?

Replies
6
Views
142
Replies
6
Views
203
Replies
5
Views
229
Replies
3
Views
243
Replies
4
Views
210

1,140,999
Messages
5,703,645
Members
421,307
Latest member
morrden86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back