Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

Get the Current Date in Excel
How to get the current date using Excel.  This method updates the date every day so that it is always accurate. To ...
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 ...
Calculate Someone's Age in Excel
How to automatically calculate someone's age using Excel.  This method is simple and will update every year so tha ...

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.

Thanks


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.


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,

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


Thanks


Hi,

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


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.

thanks,




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

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
Phil


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

Thanks!


Hi,

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

Thanks
merry_fay


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

I put a revision date in a cell (today's date) when I change a worksheet. If I merely print, view or something without actually changing the worksheet, I do not update the revision date even if I happen to save it again.

I need the worksheet to automatically update the worksheet revision date only once for that day and only when the worksheet is changed (or only once after it's opened & changed will work just as well).

I am trying to avoid changing the date every time the worksheet is changed since it's not necessary to change the date to the same date every time a change is made. More importantly, changing the revision date every time the worksheet is changed causes data entry to be slower.

I prefer not to use the today function in a cell that changes every time the worksheet is opened because the worksheet is not updated every time it's opened. Also, doing so causes Excel to prompt to save on exit of the worksheet when all I've done is printed or viewed the data.

Any ideas?

mikeburg


Hello Everyone,

I did a couple searches on this forum but was unable to find something to fit my needs.

In Excel 2003, I have a cell in column 3 that is updated with either a "P, F, CB, A or n/a" using a List Validation. I know how to show a date last modified, but my boss wants to see the date last modified based on what criteria it was changed to.

So I have four columns to the right of column 3 labeled as "P, F, CB, A" (n/a is not needed). Under each of these columns, I'd like to show the date modified when column 3 is changed to that condition.

So once column 3 is changed to "P", I want column 4 (P) to be changed to that date modified. Once column 3 is changed to "F", I want column 4 to not change and I want column 5 (F) to change to the current date modified... I hope that makes sense

Any help would be awesome! Thank you!


Dear Excel boffins

I would like to have a macro that automatically add a comment to a cell whenever the contents are changed. This is only necessary when a value is changed, not where the content changes as a result of a value that changed. The comment only need to contain the user name of whoever edited the value, and as a bonus the date and time.

Thanks a lot!


Dear VBA boffins

I would like to have a macro that automatically add a comment to a cell whenever the contents are changed. This is only necessary when a value is changed, not where the content changes as a result of a value that changed. The comment only need to contain the user name of whoever edited the value, and as a bonus the date and time.

Thanks a lot!


I am using MS Office 2003. I have a query which pulls data from Access to Excel. I need to run the query everyday and change the date to yesterday's date so that it only pulls data for yesterday.

I am having to update query manually and enter yesterday's date. Is there any way I can automate that. Or at least, can I enter a value in excel itself and if the date value in a cell is changed, the query will automatically run and display result based on the entered date.

Please help!




How do I execute a sub when a date value in a specific cell is changed?

The date in the cell is changed from VBA, no manual cell entry, change and exit. And calculation is set to manual; will the sub still execute or will it only execute when the sheet(s) is recalculated?

I am trying to create a spreadsheet that has a different time and date stamp for each row. I used the NOW() function, but each time data gets entered on a diffferent row, all the cells that have the NOW() function change time. I want to have the date/time automatically entered when a cell it is linked to is changed, and for that date/time to remain static until the linked cell is changed again, but still allowing other cells to update dates/times when they are changed. Any ideas?


Hi all, I really hope someone can help me. I am relatively experienced with the basic functionality of Excel but not being a developer by trade, I am struggling to be able to do a lot of the things that I want to.

I have a spreadsheet (which is basically a defect log) and what I want is to change a cell (in column N, the "Date Fixed" column) to show the date when the Status Column (Column K) is changed to Closed.

i.e. when I close a defect, without then having to enter today's date in the date fixed column, is there a relatively easy way to get this to happen automatically on the change of status?

Would really appreciate any suggestions.




i have asked this before some where but never got a reply and i'm still struggling with it .
bail me out pls

i will like to accomplish this through excel macro

in my workbook sheet1, values are entered in Range (A2:H20) through formula.
i also have Range (J2:P20) that will keep track of change of values in Range (A2:H20) with a Date stamp in this format "dd-mmm-yyyy"
Now i want to do this
if a value is changed in A2 the date stamp should reflect in J2
if a value is changed in A3 the date stamp should reflect in J3

if a value is changed in B2 the date stamp should reflect in K2
if a value is changed in B3 the date stamp should reflect in K3

and that goes the same with other cells within those ranges
the date stamp should replace any existing date(if any one exist) in its respective cells
if any cell in the Range (A2:H20) is =0 or empty there should be no date stamp in the corresponding cell

thanks in advance