Find number of months in a specific year a date has been running?

[SkiliedSin]

Hello all,

I’m trying to find a function that’ll give me how many months a cell has been running for in a specific calendar year disregarding the start date.

For example, for different cells containing start dates 1/1/19 and 1/1/20, I would want to find how many months they were running in 2020. Considering I have columns 1/31/20, 2/28/20 etc through 12/31/20, I am looking for a function that’ll produce 1,2 through 12 for each of those start dates I mentioned earlier.

 

[SkiliedSin]

Thank you so much!

And will do in future instances, Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.

You need to use Joe's formula as mine is wrong.

 

[SkiliedSin]

Start Date

1/31/2021

2/28/2021

3/31/2021

4/30/2021

5/31/2021

6/30/2021

7/31/2021

8/31/2021

9/30/2021

10/31/2021

11/30/2021

12/31/2021

3/1/2017​

0​

0​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

1/1/2018​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

6/1/2018​

0​

0​

0​

0​

0​

1​

2​

3​

4​

5​

6​

7​

Hello all, I was hoping you guys could help me again. As you can see for the start date 3/1/2017 & 6/1/2018, the function isn't outputting the same numbers as 1/1/2018 which is what I'm looking for here.

 

[Fluff]

What are you expecting to see?

 

[SkiliedSin]

The same outputs as 1/1/2018 as the other two dates are all running through 2021

 

[Fluff]

Is this what you mean

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Start Date	31/01/2021	28/02/2021	31/03/2021	30/04/2021	31/05/2021	30/06/2021	31/07/2021	31/08/2021	30/09/2021	31/10/2021	30/11/2021	31/12/2021
2	01/03/2017	1	2	3	4	5	6	7	8	9	10	11	12
3	01/01/2018	1	2	3	4	5	6	7	8	9	10	11	12
4	01/06/2018	1	2	3	4	5	6	7	8	9	10	11	12
5	01/06/2021	0	0	0	0	0	1	2	3	4	5	6	7
Main

Cell Formulas
Range	Formula
B2:M5	B2	=IF(YEAR($A2)<YEAR(B$1),MONTH(B$1),MAX(MONTH(B$1)-MONTH($A2)+1,0))

 

[Joe4]

I misinterpretted when you said "ignore the start date".
I can see now that you must have meant ignore the "day", but not the "month" or the "year".

 

[SkiliedSin]

Is this what you mean

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Start Date	31/01/2021	28/02/2021	31/03/2021	30/04/2021	31/05/2021	30/06/2021	31/07/2021	31/08/2021	30/09/2021	31/10/2021	30/11/2021	31/12/2021
2	01/03/2017	1	2	3	4	5	6	7	8	9	10	11	12
3	01/01/2018	1	2	3	4	5	6	7	8	9	10	11	12
4	01/06/2018	1	2	3	4	5	6	7	8	9	10	11	12
5	01/06/2021	0	0	0	0	0	1	2	3	4	5	6	7
Main

Cell Formulas
Range	Formula
B2:M5	B2	=IF(YEAR($A2)<YEAR(B$1),MONTH(B$1),MAX(MONTH(B$1)-MONTH($A2)+1,0))

This is exactly what I'm looking for!

I appreciate you and Joe4 for being so patient with me haha, sorry for the confusion and thank you again!

 

[Fluff]

Glad we could help & thanks for the feedback