+ Reply to Thread
Results 1 to 6 of 6

How to change cell value based on 12 months rolling date on other cell

  1. #1
    Registered User
    Join Date
    08-27-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question How to change cell value based on 12 months rolling date on other cell

    Hi Folks,

    I am a newbie of this forum (my first post). I was reading around and could not find my answer. So here is my problem:
    I have second row (B) on my spreadsheet showing date (12 months rolling) and on the cells beside (D "Innocent") which currently are populated with number of occurrences (usually number "1" or just empty) I would like the number "1" to be erased or zeroed if date column value is older than 12 months rolling (=TODAY()-365). Please see the example attachment. I would appreciate your help. My excel version is 2010.
    Attached Files Attached Files
    Last edited by buznac; 08-27-2013 at 09:50 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: How to change cell value based on 12 months rolling date on other cell

    Hi,

    Not knowing the format of your workbook, a formula like this should do the trick in cell D2, then drag it down as far as is necessary (you would then hide column C and use it as a helper column):
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If not, can you please upload your workbook so that I can take a look at your formulas in place?

    Thanks. Hope this helps

  3. #3
    Registered User
    Join Date
    08-27-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to change cell value based on 12 months rolling date on other cell

    Hi ajryan88. Thanks for the reply. Can you elaborate a bit. I attached my spreadsheet as an example.

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: How to change cell value based on 12 months rolling date on other cell

    OK so your spreadsheet isn't set out as I originally pictured in my head.

    What I suggest doing is copying and pasting values only into column C (the black "line" between the "DATE" and "Innocent" columns), and then inserting my formula from above into column D.

    Hope this helps

  5. #5
    Registered User
    Join Date
    08-27-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to change cell value based on 12 months rolling date on other cell

    Thank You so much it works.

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: How to change cell value based on 12 months rolling date on other cell

    No worries, happy to help!

    Please don't forget to click on the * next to my post to say thanks

+ 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. I need cell to automatically change color based on date in different cell
    By acooley in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-27-2013, 04:55 PM
  2. Replies: 11
    Last Post: 07-15-2012, 09:15 AM
  3. Replies: 0
    Last Post: 06-27-2012, 08:38 PM
  4. [SOLVED] FORMULA TO INCREMENT 25 MONTHS BASED ON A DATE CELL
    By SorianoP in forum Excel General
    Replies: 4
    Last Post: 08-14-2006, 06:50 PM
  5. [SOLVED] How to change cell colour based on numer of months?
    By Tiago in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-11-2006, 08:25 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