Date Modified Based On Cell Range


New Member
Jul 27, 2010
Hi All,

I have a spreadsheet used for tracking product. To simplify it basically i have a list of product in column a. Column b is known as my issue column. Items that don't have an issue stay blank. Items that have an issue have a "1" entered into this column. When an issue is resolved, this is then changed to a zero via manual input. What i need to do is:

In just one cell i need it to display the date that last time an issue was resolved.

So in essence i need: when a 0 is entered anywhere in column b, cell ?? needs Now() inserting into the cell.

I was hoping to do this somehow without a macro but can't think how. If anyone has any ideas either way would be fantastic.


Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.


MrExcel MVP, Junior Admin
Aug 1, 2002
Office Version
Welcome to the Board!

I am afraid that this will require VBA, specifically a Worksheet_Change event procedure that is automatically triggered upon data entry (for more on Event Procedures, see:

The VBA code would look something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
'   Check to see if only one cell is selected and you are in column B
    If Target.Column = 2 And Target.Count = 1 Then
'   If the selected cell is zero, update cell C1 with current date/time
        If Target.Value = 0 Then Range("C1").Value = Now()
    End If
End Sub


New Member
Jul 27, 2010

thanks for the reply. I've tried out ur method along with a circular referancing method I have created which also works. Now just to decide which operates best...

Thanks again.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...