+ Reply to Thread
Results 1 to 2 of 2

Time since last changed in cells.

  1. #1
    Registered User
    Join Date
    09-09-2016
    Location
    DC
    MS-Off Ver
    2003
    Posts
    1

    Red face Time since last changed in cells.

    Hello,

    I have one cell call "Above" and the other one call "Below".
    Sometimes, "Above" will change to "Below" and vice versa.

    I want to be able to tell how many days since the cell changed from "Above" to "Below"


    For example:

    Cell A1 change from "Above" to "Below" on day 1------> Cell B1 = 1
    Cell A1 stays the same on day 2-------------------------> Cell B1 = 2
    Cell A1 stays the same on day 3-------------------------> Cell B1 = 3

    Cell A1 change from "Below" to "Above" on day 4------> cell B1 = 1

    I have no idea if any formula can do this or will it required some sort of coding? Thanks for your help!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,046

    Re: Time since last changed in cells.

    The correct solution depends on if A1 is a formula, or a value that is entered manually.

    If it is manually entered:

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed

    Please Login or Register  to view this content.
    If it is a formula, you need another cell to keep track of the value returned by the formula: with this example, that other cell is A2:

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed

    Please Login or Register  to view this content.
    You could also use this in place of the formula I use

    .Formula = "=TODAY() - " & CLng(Date) - 1

    but having the DATEVALUE in the formula keeps a record of the date of the last change.
    Last edited by Bernie Deitrick; 09-09-2016 at 01:44 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

+ 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. Replies: 5
    Last Post: 10-22-2015, 01:30 PM
  2. auto-lock cells after time is entered so that it cannot be changed
    By VanHardy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-16-2013, 05:19 PM
  3. [SOLVED] Excel 2010 Identifying data changed in cells and Identifying the changed rows
    By SandyLake in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2013, 01:12 AM
  4. Display the date and time each time the data in a cell range is changed
    By BVZM in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2012, 01:23 PM
  5. Hiighlight the changed cells which are changed with the linked wookbook?
    By lb1900 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-09-2012, 12:26 PM
  6. Replies: 3
    Last Post: 01-09-2012, 01:39 PM
  7. Macro to color bacground of the cell and log time if cells content changed
    By hunsnowboarder in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-22-2008, 03:22 PM

Tags for this Thread

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