+ Reply to Thread
Results 1 to 7 of 7

Ignore Months In The Future

  1. #1
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    129

    Ignore Months In The Future

    Hi

    I have a table with data entered in columns by month starting in April of each year. The data in each cell is referenced from elsewhere but, what I want to do is add a check in the formula of each cell to only add data in the columns for the months that have passed. So, in the attached worksheet all data would be populated except for the last column, March, which we haven't yet got data for as it's now on February (just). And it needs to be blank, not show zero as the data will be used in a line chart.

    I am currently using =IF(MONTH(TODAY())>C$43,"",C34) where C34 is an example of the cell reference for the data but, where I've put "" to try and populate the cell with blank it's still returning a zero.

    Not sure if I've explained this too well but any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    129

    Re: Ignore Months In The Future

    Forgot to say, C$43 in the above post should read A$1 which is a reference to the month as in the attachment.

  3. #3
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: Ignore Months In The Future

    Please ensure that A$1 is date formatted.
    Like my answer, then click * below

  4. #4
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    129

    Re: Ignore Months In The Future

    It is date formatted in my original spreadsheet, I just forgot to format it in the example attachment.

  5. #5
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: Ignore Months In The Future

    Try this

    =IF(MONTH(TODAY())<MONTH(L$1),"",M34)

  6. #6
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: Ignore Months In The Future

    And this can be used as better way as follows...

    =IF(TODAY()<L$1,"",L5)

    Note: Ensure that all the dates in the row 1 are beginning of the month e.g. 1-jan-13, 1-feb-13, 1-mar-13...etc..

  7. #7
    Forum Contributor
    Join Date
    07-02-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    129

    Re: Ignore Months In The Future

    Thanks for those, they were helpful and work.

    But, is there anyway I could accomplish this without having to enter the full date in Row A because I want to re-use this same worksheet from scratch on the first of April each year and, with the above I would have to go to each month and change them for the current year.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to Translate Nr. of Months into a Future Date
    By BSquared18 in forum Excel General
    Replies: 9
    Last Post: 08-24-2013, 09:09 AM
  2. how do I set monthly totals, but keep future months at zero?
    By Neutrino Two in forum Excel General
    Replies: 3
    Last Post: 12-16-2011, 02:59 AM
  3. Pivot table for future none existent months
    By bg18461 in forum Excel General
    Replies: 4
    Last Post: 08-25-2006, 10:06 AM
  4. formula to remind me of an appointment 3 months in the future
    By jamaicanbashi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-13-2006, 07:45 AM
  5. [SOLVED] Networkdays & future months
    By denise in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-01-2006, 07:35 AM

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