# count number of months

#### BrutalLogiC

##### Active Member
Hi all please can you help

I'm trying to write a formula for the cells in yellow which counts the number of whole months in a calendar year based on the start date, e.g. start date 1st July 2019... there are 6 months in 2019 from start date. The numbers in there I've just manually worked out.

Book1
HIJKL
327Number of whole months in the following years:
328Start date:2019202020212022
32901-07-196121212
33001-01-200121212
33101-12-191121212
33201-03-21001012
33301-08-220005
33401-02-1911121212
33501-09-2100412
33606-07-195121212
33723607489
Summary

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### bobsan42

##### Well-known Member
Put this i I329:
Code:
``=IF(\$H329>=DATE(I\$328,12,31),0,DATEDIF(MAX(\$H329-1,DATE(I\$328,1,1)-1),DATE(I\$328,12,31),"m"))``
and fill right and down

#### AlanY

##### Well-known Member
try this in I329, copy down and across

=IF(YEAR(\$H329) < I\$328,12,IF(YEAR(\$H329)=I\$328,12-MONTH(\$H329)+1,0))

Last edited:

#### bobsan42

##### Well-known Member
Put this i I329:
Code:
``=IF(\$H329>=DATE(I\$328,12,31),0,DATEDIF(MAX(\$H329-1,DATE(I\$328,1,1)-1),DATE(I\$328,12,31),"m"))``
and fill right and down
Unfortunately this will only work if start date is 1st of the month - for example a different result will show in cell I336.

Thank you both!

you're welcome

#### bobsan42

##### Well-known Member
I don't like to copy other people's lines but: you're welcome

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,627
Messages
5,838,440
Members
430,548
Latest member
hh_dh2001

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