This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 28.000000000000004%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDC%3C/text%3E%3C/svg%3E)
I've already looked up adding datestamps in Excel. Not a problem. I already have a table with data in it. I don't want to display the current date if there is already data in the cell. I only want the datestamp to update when the other cell value changes, but I do want a default date for the unchanged data. So, here is the example,...
Suppose I already have a value of 1000 in cell A2 and a blank in cell A3. I want a default date in cell B2, but if cell A2 changes to 1001, I want the current date in B2. For cell B3, I want it to be a blank until data is entered/updated into B2.
How can this be done?
Hi @Daniel Checkman
Do values in A2 not just 1000 or 1001?
If so, in my opinion, your requirements need the VBA help.
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
To add a datestamp to a cell in Excel when the value in another cell changes, you can use the following formula:
=IF(A2<>A2, "", TODAY())
This formula will return a blank value if the value in cell A2 has not changed, and will return the current date if the value in cell A2 has changed. You can then copy and paste this formula into the other cells in column B to apply the same logic to those cells.
Here's an example of how you could use this formula:
In cell B2, enter the formula =IF(A2<>A2, "", TODAY()). This formula will return a blank value if the value in cell A2 has not changed, and will return the current date if the value in cell A2 has changed.
In cell B3, enter the formula =IF(A3<>A3, "", TODAY()). This formula will return a blank value if the value in cell A3 has not changed, and will return the current date if the value in cell A3 has changed.
Repeat this process for the other cells in column B as needed.
Good luck!
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(246.4, 50%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ED%3C/text%3E%3C/svg%3E)
Greetings, I have a related / follow-on question:
Can you modify this formula for a range of cells, where the date updates for any changes in the range? e.g.: "=IF(A2:A12<>A2:A12, "", TODAY())" for changes in the range A2:A12? (This particular attempt resulted in a SPILL error).
Also, the provided formula populates the date if there is any data in the cell. I require it to update iif one of the cells in the range is updated (automatically by a COUNTIF formula, in most cases).
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 77%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Trying to use the code suggested on my own spreadsheet and have the formula below which appears to do everything requested apart from the don't add a date until something changes which was my next intended tweak but the formula malfunctions on next call of the today function
=IF(N21<>"",(IF(N21<>N21,"",(TEXT(TODAY(),"DDD DD MMM YYYY")))),"")
at first this appears to do everything requested in the formula but when I change any cell the date will be updated in all cells showing a date from the today function including those linked to cells that have not changed. Can anyone point me in the right direction to stop this behavior. My thinking at the moment is to add a comparison cell and use some circular references which is not going to be ideal and could cause buggy behavior
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
How can the expression N21<>N21 ever evaluate to anything but FALSE?
This formula does not check to see if a value has changed. It only checks to see if the cell is empty.
Once you change any cell, the worksheet will recalculate every cell. All the cells with that type of formula will have their dates recomputed to today if the referenced cells are not empty.
You will need Worksheet Change Event macro to determine if the cell being changed is one you are interested in and , if so, then set a new value in the cell where you currently have the formula that references that cell.
Updated solution - have resolved being able to update entries immediately
For anyone else that is finding macros don't run on a works configured computer my current version of formula for this tweaked to stop it updating on every call of today function from what was previously shared is
Since the cell references itself in the formula you will need to enable file - options - formulas - enable iterative calculation turned on for this formula to work. I've set this to 2 iterations which is working so far.
When configuring this for your own table Column G contains the Data being monitored and Column H is the column containing the formula below where date is shown. Column I is a comparison field this column can be resized / hidden from the user
This formula gives todays date for any cell you enter/update data or returns a default date of 1st Jan 1901 if data was already present the day you entered the formula. you can change this by altering the number 367 to the number associated to whatever date you would like to show. The number 45643 is the value returned by the =today() command - this should be set to whatever number is returned the day you add this formula to your spreadsheet.
the text function sets the date formatting into readable format and removes need to configure this in formatting. It also I believe will override any formatting set elsewhere using the format command that may otherwise break the formula comparisons if changed.
Formula in column I
=IF(G4="","",IF(H4=(TEXT(367,"DDD DD MMM YYYY")),G4,IF(H4=(TEXT(TODAY(),"DDD DD MMM YYYY")),G4,I4)))
Formula in column H
=IF(G4<>"",IF(H4<>"",IF(G4<>I4,(TEXT(TODAY(),"DDD DD MMM YYYY")),H4),IF(TODAY()=45643,(TEXT(367,"DDD DD MMM YYYY")),(TEXT(TODAY(),"DDD DD MMM YYYY")))),"")
Recommended typing these formulas out in a row with no data for cell being monitored and then use the fill function on comparison column first then date column to avoid incorrect results showing
When you add data on the day you fill out the formula to get todays date to show making any second change will trigger todays date instead of default old data date value being shown
Again thanks to all the other contributors - without the hints and tips they've shared this solution wouldn't exist
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Just spent way to long listening to a hunch there was a mistake and looking for an error in my spreadsheet where everything is working only to finally realize i'd posted the same formula twice as solution - now corrected.
Formula for column H is actually
=IF(G4<>"",IF(H4<>"",IF(G4<>I4,(TEXT(TODAY(),"DDD DD MMM YYYY")),H4),IF(TODAY()=45643,(TEXT(367,"DDD DD MMM YYYY")),(TEXT(TODAY(),"DDD DD MMM YYYY")))),"")
This might also be useful, you can lock formulas to protect against accidental changes
to do this select all cells - format cells - protection - unticked locked box and accept changes
next highlight all cells you want to protect and go back to formatting settings to re-tick the locked box for those cells
Next click on Review tab and select protect sheet - you can also add a password if you need to then select both the locked cells and unlocked cells tick boxes and accept changes
you will now get a popup error if you try to change contents of the locked cells
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(208, 28.999999999999996%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
Another formula in similar vein to above that this time sets date range of cells has a number entered greater than 0 that others may find useful
formula entered in cell N1, this again needs iterative formulas turned on
=IF(MAX(N2:N100)>0,IF(N1="",TEXT(TODAY(),"DDD DD MMM YYYY"),N1),"")
no comparison cell needed to make this one work so simpler to add correctly as ignores non number entries and when values are updated
Below is same formula but this time forces some readability wrap text into the text formatting outputted and works when wrap text has been enabled for the cell
=IF(MAX(O2:O100)>0,IF(O1="",TEXT(TODAY(),"DDD"&CHAR(10)&"DD MMM"&CHAR(10)&"YYYY"),O1),"")
Hi Daniel
I can do what you want if you compare column A to another column. It can be in the same sheet or another sheet
eg:
Column A Your Data, then copy your Data to Column E
This way if you change anything in column A the macro will detect the difference between the two columns and stamp date and time in column B.
This is a sample Macro that compairs column A with Column E
Try it in a test worksheet and see if that will work for you.
You may need to format your date and time.
This is an Event Macro, it goes in the Worksheet not in a Module but We can change it if you like.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Variant, LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For Each cel In Range("A2:A" & LastRow)
If cel.Value <> cel.Offset(0, 5) Then
cel.Offset(0, 1) = Now
End If
Next cel
End Sub