# How to count months (Previous & Next) based on particular date

1. ## How to count months (Previous & Next) based on particular date

Hi Folks,

Hopefully you guys can help since I've got a stuck to get previous 17 months and next 5 months from one particular date with first day of month. I have attached a file of what I'm trying to accomplish. Thanks in advance for your expertise!

Joshi

2. ## Re: How to count months (Previous & Next) based on particular date

Checkout DATEDIF()

=DATEDIF(A1,B1,"M")

http://www.cpearson.com/excel/datedif.aspx

Make sure that your first date is always less than your second date, otherwise you'll get an error.

***EDIT***
I just re-read your post and looked at your workbook.

You may be requesting something like this:

=DATE(YEAR(A1),MONTH(A1)+1,1)

3. ## Re: How to count months (Previous & Next) based on particular date

Hi Whizbang,
Thanks for your reply. But its not suite for my needs. Please open the attached workbook and read the comments. I hope u can help me.

Joshi

4. ## Re: How to count months (Previous & Next) based on particular date

Please see the attached.

Unmerge the cells in row 5

In C4 enter the following formula and copy across:
=IF(SUM(IF(YEAR(\$C\$5:C\$5)=YEAR(C5),1,0))=INT(SUM(IF(YEAR(\$C\$5:\$Y\$5)=YEAR(C5),1,0))/2),YEAR(C5),"")
NOTE: The above formula is confirmed with CTRL+SHIFT+ENTER, not just ENTER.

In C4:Y4 enter the following conditional formatting formula, and set fill color as desired.:
=YEAR(\$K\$2)<>YEAR(C5)

In C5 enter the following formula:
=DATE(YEAR(\$K\$2),MONTH(\$K\$2)+C6,1)

5. ## Re: How to count months (Previous & Next) based on particular date

Hi Whizbang,

Thanks a lot, its working excellent. One small issue can you help me to put vertical border line in between the Year Header (e.g. End of 2011 and start of 2012).

Joshi

6. ## Re: How to count months (Previous & Next) based on particular date

In C4:Y4 add a conditional formatting rule with the following formula:
=MONTH(C5)=12

and set the right border only.

Or
=MONTH(C5)=1

with the left border only

7. ## Re: How to count months (Previous & Next) based on particular date

Hi Whizbang,

One more small issue the first Year Header not appearing (Please see the attache workbook)

Joshi

8. ## Re: How to count months (Previous & Next) based on particular date

Change the formula in C6 to:

=IF(SUM(IF(YEAR(\$C\$7:C\$7)=YEAR(C7),1,0))=ROUNDUP((SUM(IF(YEAR(\$C\$7:\$X\$7)=YEAR(C7),1,0))/2),0),YEAR(C7),"")
(Confirm with CTRL+SHIFT+ENTER)

Then copy the formula across.

9. ## Re: How to count months (Previous & Next) based on particular date

Thanks a lot. Its working great.

Joshi

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1