Date interval in months with a twist

[ChrisM]

Hello, trying to figure out the number of months in a date interval, but only for the year in which I am interested.

Say the interval is 9/1/2012 thru 10/31/2014

Now I have four cell values I want to return, how many months are in the years 2011, 2012, 2013, and 2014? The answer would be:

2011 - 0
2012 - 4
2013 - 12
2014 -10

Any thoughts on how to turn that into a formula?

I'm not hung up on the day of the month. All entries can be normalized to the first day of month for simplicity.

Thanks!

 

[kpark91]

Hi, if your starting date is in Cell A1
and ending date is in Cell B1

and your table starts from A3:B6
Put this formula in B3 and copy down
=IF(OR(A3 < YEAR($A$1), A3 > YEAR($B$1)), 0, IF(A3=YEAR($A$1), 12-MONTH($A$1),IF(A3=YEAR($B$1), MONTH($B$1),12)))

 

[ChrisM]

Very nice, thank you! I had over-thought that one, was thinking it would need an array or fancy function. Turns out it just needed some good logic.

Thanks!

 

[kpark91]

Very nice, thank you! I had over-thought that one, was thinking it would need an array or fancy function. Turns out it just needed some good logic.

Thanks!

The part of the reason why it's just a nested IF statements is because I don't know how to make fancy functions loolll.

Thanks for your feedback.
Glad it helped.

 

[ChrisM]

I've run into a snag. The way the formula is written, if my start and stop dates are in the same year, the formula will return incorrect month count in many situations. I could add more nested If-Thens to test, but I'm curious if there is a cleaner way to do this.

Here's the problem:

IF(A3=YEAR($A$1), 12-MONTH($A$1)

This doesn't consider $B$1. If the date range ends mid year then the reported months are wrong.

I can see how to add more tests to prevent this, but if there is a completely different way to handle this scenario I would be interested. I don't want to use so many nested Ifs.

 

[kpark91]

I've run into a snag. The way the formula is written, if my start and stop dates are in the same year, the formula will return incorrect month count in many situations. I could add more nested If-Thens to test, but I'm curious if there is a cleaner way to do this.

Here's the problem:

IF(A3=YEAR($A$1), 12-MONTH($A$1)

This doesn't consider $B$1. If the date range ends mid year then the reported months are wrong.

I can see how to add more tests to prevent this, but if there is a completely different way to handle this scenario I would be interested. I don't want to use so many nested Ifs.

Ahh, I forgot about that scenario.
You could always add more IF statements like you've said but I am interested in fancier formula as well.

Any takers?

 

[kpark91]

anyways, here's the updated crappy If statement formula.

=IF(OR(A3 < YEAR($A$1), A3 > YEAR($B$1)), 0, IF(YEAR($A$1) = YEAR($B$1), MONTH($B$1) - MONTH($A$1), IF(A3=YEAR($A$1), 12-MONTH($A$1),IF(A3=YEAR($B$1), MONTH($B$1),12))))

 

[xenou]

I didn't find much better (just a variation on the same idea):

=IF(A3=YEAR($A$1),13-MONTH($A$1),0)
+IF(A3=YEAR($B$1),MONTH($B$1),0)
+IF(AND(A3>YEAR($A$1),A3<YEAR($B$1)),12,0)

(Note: in KPark's last formula we need to add 1 here: 12-MONTH($A$1), otherwise we get 0 months for December and 11 months for January).

 

[ChrisM]

Thanks for taking a stab at it, sounds like this is the best way to go.

 

[Jared_8878]

just more alternatives,

if your dates are in A1:A26
and put
2010
2012
2013
2014

in C1:C4

Then in D1 put in:

=COUNT(IF(YEAR($A$1:$A$26)=C1,1))

and when you enter it use ctrl+shift+enter

The second method is you can select A1:A26, and insert a pivot.
Put the only field that shows up in your pivot table both in the values section and the row labels section, then on your row labels group by year.