count number of months

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.
 

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
266
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you both!
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
I don't like to copy other people's lines but: you're welcome :)
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top