Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 190
Default Last Modified date on cell or column

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Last Modified date on cell or column

Good morning! Yes, there is a way to automatically track the last modified date of a cell, column, or row in Excel. You can use the built-in function "NOW()" to capture the current date and time, and then use a simple VBA code to update the "Last modified date" column whenever a cell in the corresponding row is changed.

Here are the steps to set this up:
  1. Open your Excel worksheet and select the column where you want to track the last modified date.
  2. Right-click on the column header and choose "Insert" to add a new column next to it. This will be your "Last modified date" column.
  3. In the first cell of the "Last modified date" column (B2 in your example), enter the formula "=IF(A2<"",NOW(),"")". This will display the current date and time whenever the corresponding cell in column A is not empty.
  4. Press "Enter" to apply the formula to the cell.
  5. Select the cell with the formula (B2) and copy it.
  6. Select the entire "Last modified date" column (B2:B7 in your example) and paste the formula by pressing "Ctrl+V".
  7. Now, whenever you change a cell in column A, the corresponding cell in column B will update with the current date and time.

To make this process automatic, you can use a VBA code that will run whenever a cell in column A is changed. Here's how:

1. Press "Alt+F11" to open the Visual Basic Editor.
2. In the Project Explorer window, double-click on the worksheet where you want to track the changes.
3. In the code window that opens, paste the following code:

Formula:
Private Sub Worksheet_Change(ByVal Target As Range)
    If 
Not Intersect(TargetRange("A:A")) Is Nothing Then
        Target
.Offset(01).Value Now()
    
End If
End Sub 
4. Save the workbook and close the Visual Basic Editor.

Now, whenever you change a cell in column A, the corresponding cell in column B will update with the current date and time automatically, without you having to type anything.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Last Modified date on cell or column

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 190
Default Last Modified date on cell or column

Thank You. Very Helpful.

"Gary''s Student" wrote:

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 190
Default Last Modified date on cell or column

Gary,
The macro works fine but there is any other way to set a date without
running a macro?

"Gary''s Student" wrote:

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Last Modified date on cell or column

If you want a static date, you can select the cell and hit ctrl-; (control
semicolon).

If you want a static time, you can hit ctrl-:



Mary wrote:

Gary,
The macro works fine but there is any other way to set a date without
running a macro?

"Gary''s Student" wrote:

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007


--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Last Modified date on cell or column

Hello,

This works for me, but it has a side effect of not allowing any un-dos.
After changing the tracked cell, CTRL-Z does not do anything. Is there a way
to preserve the undo history?

"Gary''s Student" wrote:

Let's say we enter data in column A and want column B to mark the dte of last
update. So if we enter data into A5, then B5 would automatically record the
date this update occurred. in Worksheet code enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End Sub

REMEMBER: Worksheet code.
--
Gary''s Student
gsnu200710


"Mary" wrote:

Good Morning,
Can you please advise if there is a way or formula to know when was the last
time a cell, column or row was modified without typing everytime that I
update it.
Example:
A B
Data Last modified date
BER 3/12/2007
BER 3/1/2007
BER 2/18/2007
SPR 2/18/2007
SPR 2/18/2007
PHL 2/18/2007

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Recording the date another cell is edited or modified. Ed Excel Worksheet Functions 2 December 2nd 06 04:22 AM
how to show the date a spreadsheet was modified in a cell k2224z Excel Discussion (Misc queries) 1 August 28th 06 11:25 PM
Show date cell modified manxman Excel Worksheet Functions 3 March 23rd 06 12:48 AM
In adjacent cell, show last date modified of target cell. manxman Excel Discussion (Misc queries) 0 March 17th 06 11:47 PM
Sheet Modified date in a cell.. ? n666 Excel Worksheet Functions 0 February 16th 05 02:32 AM


All times are GMT +1. The time now is 08:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"