Date Modified Based On Cell Range

everton1878

New Member
Joined
Jul 27, 2010
Messages
2
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.

Thanks
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,982
Office Version
  1. 365
Platform
  1. Windows
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: http://www.cpearson.com/excel/Events.aspx

The VBA code would look something like this:
Code:
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
 

everton1878

New Member
Joined
Jul 27, 2010
Messages
2
Hi,

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,113
Messages
5,857,454
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top