+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Thumbs up 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
    Attached Files Attached Files
    Last edited by krjoshi; 11-28-2011 at 01:18 PM.

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    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. #3
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    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. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    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)
    Attached Files Attached Files
    Last edited by Whizbang; 11-23-2011 at 03:47 PM.

  5. #5
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    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. #6
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    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. #7
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Exclamation 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
    Attached Files Attached Files

  8. #8
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    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. #9
    Forum Contributor
    Join Date
    10-30-2011
    Location
    Doha
    MS-Off Ver
    MS office 2010
    Posts
    693

    Thumbs up Re: How to count months (Previous & Next) based on particular date

    Thanks a lot. Its working great.

    Joshi

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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