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 Tutorials

Join a Date and Time into a Single Cell using VBA Macros in Excel
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
Quickly Enter the System Date in Excel - Keyboard Shortcut
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...

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

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 am trying to automatically add a comment to each cell, to show when the cell was last changed. I have the following VBA,

Please Login or Register  to view this content.

which updates an individual cell's comments with "Last Modified (date)" whenever the cell content is changed. It works just fine if I enter a value into a cell, or copy and paste values of one cell to another cell. But it does not work if I change multiple cells, such as copying and pasting a range.

How do you get this to work on a range of cells?

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 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.


Ok so what I'm trying to do is have date automatically inserted whenever you click on a cell in column A.
We have an excel workbook that we use to enter leads that we get in at work to keep track of all the leads.

I'm getting tired of having to enter the date in everytime and I know that I can use the "=Today()" formula to populate the date but I don't want to enter that in everytime. What I want to be able to do is whenever we get a lead in I want to open the book and select the first cell in the next row and have it automatically enter in the current date.
I'm assuming that I will need to use vba code for this but I'm not really sure how to accomplish this... If someone could help me with this that would be great!

Thanks in Advance.

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

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.

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'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 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 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.

Hey Guys,

Bit of a difficult question, which im assuming may need the help of a Macro.

Basically I have a Spreadsheet which contains hundreds of rows,
Im wanting to create a single Cell for each of those rows, which would originally be blank.
But, if any of the data is changed on a row, that specific cell for that row will change to the Date of Change (Current date) and then in the future, if the same cell is changed, to update to the new date.
Im also needing it to be able to work if new rows are added.

Difficult one I know, but hopefully someone can think of something

Some of the values will be Figures (Numbers), where others will be simple "Y"s

Thanks all!

Blink aka Matt


In our spreadsheet, each row tracks a different project. Column D can be one of 3 values (new, in progress, done.) We'd like to add (with formulas or macro) a function that populates the date into columns AA, AB, and AC, when a project's status is changed. So, when a ceratin project is changed to in progress in column D, column AB should be populated with that day's date. When it's marked done, column AC should have that day's date. Currently, we just enter into column AA the date when a new project is added.

The dates, however, have to remain true to when they were updated, so we cannot use =NOW(), since that automatically updates to today's date.

Any thoughts would be greatly appreciated.

Thank you!

I need a formula that can do 2 things,
1. insert a date into E2 when D2 is changed, and
2. Copy that date to 2!E2, 3!E2, 4!E2, 5!e2...all the way to 31!

the trickey thing is that when 2!D2 is changed, 2!E2 should be updated with the current date and then post that to 3!E2, 4!e2, so on and so forth. BUT not 1!E2.

any help is very appreciated.

edit-i need this not to respond to changes in the adjacent cell due to formulas making changes, only when users make a change. Also, it would be nice if the date is only displayed if it is more than 1 week old. im thinking this may be too complicated. but im open for any suggetions.

I am trying to do a production planning worksheet where I roll forward the
plan on a daily basis. In column A I have entered a list of dates. The first
date would be, for example today, I enter 6/28. The rest of the dates are
rolled forward by the formula for the cell above + 1. In column K I am trying
to total the orders due on the date in column A. The orders are in a separate
worksheet in the same workbook. I am trying to use a sumif formula. It works
for all the dates after the first date in the list because I am entering
(pointing) to the date in column A. However, in the first day I need to sumif
all orders that are due on that day or before that date. So, I think I need
to enter <=A2 (where A2 is the first date of the roll forward. When I enter
it this way the total is 0. I have tried entering both with and without
quotes around the formula and this doesn't help. I have changed the date in
cell A2 to be a formula =date(2006,6,28) and this does not work. I was able
to make it work by entering the critera as "<=6/28/06" however this requires
me to go in and physcially change this each time I change the beginning date
of the rollforward. Is there not a way to enter the criteria that will change
automatically when the date in column A changes?
Bob B.

I've created a list of call-ins. In column A is the Date and in column B is the time, I would like these cells to automatically populate based on the date and time information is entered in column D. I know to use a Worksheet_Change macro but I don't know how to indicate which row to populate the date and time into. My guess is that Excel knows which cell was just changed, if I could reference that I could offset or just goto column A and B of that row. I can't use current selection because some users may hit enter and some may hit tab. Does anyone know how I can reference the row of the cell that was just changed to execute the macro?