count number of months

[BrutalLogiC]

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
	H	I	J	K	L
327		Number of whole months in the following years:
328	Start date:	2019	2020	2021	2022
329	01-07-19	6	12	12	12
330	01-01-20	0	12	12	12
331	01-12-19	1	12	12	12
332	01-03-21	0	0	10	12
333	01-08-22	0	0	0	5
334	01-02-19	11	12	12	12
335	01-09-21	0	0	4	12
336	06-07-19	5	12	12	12
337		23	60	74	89
Summary

 

[bobsan42]

Put this i I329:

=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]

try this in I329, copy down and across

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

 

[bobsan42]

Put this i I329:

=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]

Thank you both!

 

[AlanY]

you're welcome

 

[bobsan42]

I don't like to copy other people's lines but: you're welcome