Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Automatically Change Date When A Cell Value Is Changed

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How can I enter a specific value in one cell and have it enter the date that value was changed in another cell?

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Create a 12 Month Calendar With The Current Day Highlighted in Excel
- Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicel

Similar Topics

Hi there, I'm trying to do something simple yet its not working properly. What I would like to happen in my worksheet is any time a cell is changed in column B, lets say for instance B22 is changed, the corresponding cell in column A, in this case A22 will change to a specific value (today's date).

Essentially I want to add values to cells in column B, whilst a column A cell will update to today's date if the corresponding cell value in B has changed.


28/04/10 Value 1 (last changed today)
10/04/10 Value 2 (last changed on 10/04/10)

where value in A is =TODAY() if I changed the value of B.

I tried doing this via:


Sub Worksheet_Change(ByVal Target As Range)

Dim cellchange As Range
Set cellchange = ActiveCell

    If Intersect(ActiveCell, Range("B1:B999")) Is Nothing Then


        cellchange.Offset(0, -1).Value = "=TODAY()"

        End If

End Sub

But this causes today's date to appear next to the value of the next cell down, if I edit the ActiveCell and press ENTER. If i edit the ActiveCell and click away then it works as prescribed. I need it to work correctly if I press ENTER after changing the cell as well.

Your help will be greatly appreciated :-)

I have the following macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 1 Or Target.Column = 2 Then
MsgBox "You just changed " & Target.Address
MsgBox "You just changed Row " & Target.Row
MsgBox "I would like to change the date in Cell " & Target.Range("C" & Target.Row).Address
Target.Range("C" & Target.Row).Value = Date

End If
End Sub

The intended purpose of this macro is to enter today's date into column "C" when a change is made to a cell in Column "A" or "B". I try to use the Row of the cell that is changed in Column "A" or "B" in order to designate what cell I would like to change in Column "C" (i.e. if I change A2 or B2, Cell C2 should have today's date). However, the above macro is placing the date in the wrong cell and column depending on what cell I choose. For instance, if I make changes to cell A2, the date in C3 is changed (the date in C2 should be changed). And when I make changes to cell B2, the date in D3 is changed (the date in C2 should be changed). Oddly enough, when I make changes to cell A1 (title row), it works (i.e. Cell C1 is changed to show today's date), but I would rather it did not take into account changes to the title row (row 1).

Any assistance is much appreciated.


I have the code that will allow me to enter a time into a cell beside the changed data. How do I alter the programming to update a specific cell when a range are changed.

I.E when a monthly update is changed in cells range c75:C100 how can I get the latest update box at A1 to change.

Help or a signpost would be appreciated.


I am looking to find out if it is possible to set up a worksheet to automaticly enter todays date when a specific cell value is changed. or when a cell is edited?

Any help would be very much appreciated.

I have data in right column sorted as date and fields below... (attachment)...

If some of cell in E column is changed (selected from drop down list in cell) I need date to change into today. If not leave last date...

First time it look at E4 where is start date entered.
A Report that you start to write.

After that it look as it explained: if changed today() if not last date entered.

If someone change start date it shouldn't affect dates below (Start date shouldn't be changed but you never know

Every date should look only cells below it up to next date.

NOt quite sure how to ask what I am asking but here it goes. I have a DTPicker on my sheet that when changed updates a cell, let's call it "Date Cell". I am finding that if I enter information into another cell, not Date Cell, and then DO NOT hit enter or tab or whatever and select and change the DTPicker, it does not update Date Cell.

If I enter info into a cell and then hit enter then select the DTPicker and change, it will update Date Cell.

I am not sure I am using the correct verbage here, but how do I force a sheet recalculation when the DTPicker is selected?

I guess because I never actually leave the cell, Excel thinks I am not done entering information.

Hope all that made sense.

Hi, Found you on the web...searching for help.

Excel 2003 workbook, I use Win XP Pro...

All of a sudden when I enter a date in to a correctly formatted cell, the date changes automatically by the program and will not "hold".

Example : when I enter 05/03/08 into the first cell in my DATE column, the program changes it to 09/25/37, the second cell when entered with 05/04/08 turns to 01/03/38, the next date, 05/05/08 becomes 04/13/38 and 05/06/08 becomes 07/22/38...weird. Note the 90 day interval in the changed date sequence.

Older worksheets, when dates are cleared then reapplied also are changing erroneously,,,,HELPPPP!!!


Is it possible to have a macro run when a date is changed each month instead of clicking on the button. I have a few macros that are ran each month in a spreadsheet (manually) but would I would like to have them run automatically when the date is changed in cell a4 each month (Cell A4 is linked to another spreadsheet tited "Input" here the date is changed each month and all of the other spreadsheets are automatically updated). The date in cell a4 is always the last day of the month. Also the name of the workbook changes each month ( I am not sure if this is important to know).

Thanks for the help


I changed the column widths in a page and now when I try to enter a date it brings up #### in the cell. How do I get rid of this? I tried the format to date and a couple of different date configurations to no avail. HELP! I have tons of data to enter.


Hi everyone, I was hoping to get some help with a spreadsheet I am creating for work. I have had this partly answered on here but theres a slight issue with it that I hope someone knows the solution to.

I have a spreadsheet with a "Yes" "No" drop down boxes set up in column G. These run down the column to row 16. What I want is for the date that the drop down box is changed to "Yes" to be entered in the cell to the right of that box. So, if the drop down box in cell G2 is changed to "Yes" today (21/02/11) the cell to the right (H2) will enter the date of when that work was completed. I have the code for this working and set up in VBA. It looks like this:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And LCase(Target.Value) = "yes" Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Date
    Application.EnableEvents = True
End If
End Sub

The only problem is when I try to change the "Yes" back to "No". When I do this the date stays as it was in the cell next to it. So if a mistake is made and the wrong cell is changed back to "No" the date remains. Does anyone know how I can make the cell to the right of the drop down blank again if it is changed back?

Any help with this will be greatly appreciated.

I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).

For instance,

1 $32.17 8/17/08
2 $43.20 9/12/09

If I go in and update cell A1 to $33.98 today I would like cell B1 to read 2/16/10. But I don't want cell B2 to change.


All you experts out there, help please....

I am fairly new to using VBA under Excel and I have a problem (which is why I am writing this, dah).

Using the script below I am trying to set it up so that when a change is made to one of the cells is a row a specific cell in that row gets the current date entered. This allows me to know when a specific row was updated.

The problem is when after making a change and I press enter the cursor jumps down one cell and then the script is run making it think the new row was the current row during that change causing the update to be entered in a cell one lower than it needs to be. If I press the left arrow key instead of enter it work fine because I am still in the same row.

How can I get the activecell to be the one that was actually changed and not the one the cursor just moved to.


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range
    Dim DateCell As Range
    Set WatchRange = Range("A3:H1000")                  'Set the range that will trigger the date change
    Set IntersectRange = Intersect(Target, WatchRange)  'Find which cells the cursor has selected within WatchRange
    Set DateCell = Range("I" & ActiveCell.Row)          'Set the cell that will be changed to the current date
    If IntersectRange Is Nothing Then
        'Do Nothing
        'Otherwise, change the DateCell to the current Date/Time
        DateCell.Value = Date
    End If
End Sub


hope someone can help, very simply I want to capture the date and time that a cell is changed.

So if the value in cell A2 is changed, then cell B2 date stamps with the current time; similiarly if cell A3 is changed, then cell B3 date stamps...

Tried looking at previous threads, I'm a bit confused by one that seems to work with a personal sub? Please can someone help, I'm no expert in macros but if there's a simple one I can use, or a formula based solution, I'd be very grateful,
thanks, Richard

Hello all,

I am new to the whole world of VB but learning things pretty quickly. I need help although with the following situation:

for example, I have data (a date) in cell A1 and I would like a macro to change the data in cell B1 when the data in cell A1 has changed. i.e if the date has been manually changed in A1, I would like the data in cell B1 to change automatically (from 0 to 1).

I have this scenario in multiple cells across multiple sheets, so I plan on copying and pasting this macro where necessary.

Is this possible?


I have a cell (A1) that I enter data into. I have another cell (A2) that I enter the date. Now, I have a range of cells (A3:A7), A3 looks at A2 if the date is true then the data in A1 goes into A3. Now what I want to do is, change the data in A1. A4 looks at A2 and if the date is true and if A3 already has data in it from A1 before I changed it then the data in A1 (the new data) goes into A4. Have I lost you yet? I want to enter data in A1 but I don't want the data in A3 to change after I change A1. I don't want to use VBA if I can get away with it. I wish I could explan this a little simpler. Any question please ask, I'll try to answer them the best I can. The ideal I have is simple I just can't find a way to do it. Everything I've tried changes A3.


Hi Everyone,
I need some VBA code that will insert the date, into a specified cell on a specific worksheet, that the worksheet was last changed.

I have a worksheet named 'points'. If any cell on the worksheet 'points' is changed I need the date that the change took place to be put in cell A1.

I cannot use the Today() function because it will change when the workbook is opened even if no changes have been made to the worksheet.

Any help would be most appreciated.

Many thanks

Hi Everyone-
Thanks in advance for reading this question-

I need to have a cell (at the end of a row) automatically insert the current date when anything in that particular row is modified.
It's a shared project status report and we want to be able to see what was changed when any other user looks at the worksheet.

This will be a worksheet on a shared folder with sharing on-

Any tips?

Also is there any way to have it automatically enter who changed that row?

I've always entered date as yy-mm-dd. In fact when I want to enter Aug 28, 2005, I would only need to enter "5-8-28".
With the proper formatting on the cells, the date will be displayed as "2005-08-25", or "050825" or whatever I have
formatted it as.

My problem started after I installed Tiger (OS 10.4) over the weekend. Now all the format has changed. I've tried to change
between the formatting on Excel, chedked the preferences as well as changing the System Preferences. But I just can't
get back to what I used to get in the date
format. The cell will display whatever I format and that's not a problem. Where the real problem is where I enter the
date. If I enter it like I've always been; "5-8-28". Instead of getting "2005-08-28", I'd get May, 8, 2028. The only way
to get around this would be if I change the way I enter the date to "month/day/year". But I've been entering date my way
for years, and I definitely don't want to have to change now.

So is there anyway to change this. so it will accept the date as the way I enter it as "year-month-day".

Thanks for any suggestions.

Hello all,

First, thx.

I am trying to add the date to a cell based upon the date that another cell has been changed.

I.e., if I type something (a comment, in this case) into cell A1, I want cell A2 to note the date that the change was made. If I enter a new comment (by deleting the previous or addending it) into A1, I then would like A2 to reflect the date of the latest change. If, at any time, A1 is blank, I want A2 to be blank as well.


First of all, all I know about VBA is how to spell it.

However, I have a chunk of code that is supposed to automatically enter today's date in cell M when "Yes" or "No" is clicked from a drop box in cell K. Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
' Check whether cells in K2:K150 have changed
If Not Intersect(Range("M2:M150"), Target) Is Nothing Then
' Temporarily turn off screen updating
Application.ScreenUpdating = False
' Loop through all changed cells
For Each rngCell In Intersect(Range("M2:M150"), Target)
' Is the cell empty?
If rngCell = "" Then
' Clear the cell next to it
rngCell.Offset(0, 1).ClearContents
' Enter the current date in the cell next to it
rngCell.Offset(0, 1) = Date
End If
' On to the next cell
Next rngCell
End If
' Turn screen updating on again
Application.ScreenUpdating = True
End Sub

I think I know the problem is the "Clear the Cell next to it" and the "Enter the current date in the cell next to it" parts. The cells have a field in the middle (L, obviously) that it would make no sense to move. How can I fix this so instead of filling cell M puts the date in N, entering in cell K would populate M? I'm sure it's embarrassingly simple.


I need help with the macro below, can it be modified so when the user answers "yes" that the date on cell "B6" is automatically changed to the 20th of the follow month. So for example if the date in cell "B6" is currently 12/20/09 and the user answers yeas to the macro then the date in cel "B6" will change to 01/20/10.


Dim a As VbMsgBoxResult, b As String
a = MsgBox("Is it time to change the workbook date?", vbYesNo + vbQuestion)
If a = vbNo Then Exit Sub
b = InputBox("Enter the new date")
Sheets("Input").Range("B6").Value = DateValue(b)
ActiveWorkbook.Protect Password:="KBS-CBRE01", Structu =True, Windows:=False

I have the following formula in a cell


So, when someone picks Yes in M7 it will enter todays date. However, the date will change to the the date it is currently. Is there any way to make the date stick as to when it was changed to "Yes"????

How can I set the value of a cell to give the current date when that cell is clicked or given focus.

I want A5 to show as blank, nothing there, until I click the cell, or somehow select the cell, and then have it show the the current date. If I want that date I hit tab or enter and it will record it.
If I don't want to record anything i can arrow past it and it will not record anything.
If I want to change it I double click or click again(If I'm in the cell already) and I can type over or just put in the date I want.

However If the user records a date in that cell and goes back to that cell do not record the default current date on tab or enter it most be double clicked in order to change it. This is a safety that they don't tab to it and past it and have it changed by mistake. Once a date is recorded they have to double click to change it.

This is not for just one cell but for the range of cells in colum A starting on A5.

I hope this makes sense.


I'm trying to run an event on the cell that has been changed in a worksheet change event.
This works in my excel where I don't move the cursor after enter, but when my colleagues use it, it then looks at the wrong cell because they move after enter.

Is there a way to say 'go to the last cell changed'?

I've got the code for Application.MoveAfterReturn = False but this will leave their setting changed (or if I set it back to true, change mine!!).


I have only an average skill level in Excel, but I have a good amout of skill
with Access and VBA.
I was wondering if there was a way to automatically record the present date
when a cell data is entered/changed. This date can be stored in the comments
field or in another cell.

I know the user could just enter the date in another cell manually but I
have been asked to make this an automatic process by my boss.