I'd like to have one cell display the current date each time another cell is edited.

[juntjoo]

Simply I'd like a cell, say A1, to display today's date each time B1 is edited. Simply problem to state but I suspect it might not be so easy to accomplish. Hopefully not. let me know. Thanks

[Richard Buttrey]

Hi,

You'll need a sheet change macro for this.

i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target,Range("B1")) Is Nothing Then
      Range("A1") = "=TODAY()"
      Range("A1") = Range("A1").Value
   End If
End Sub

[juntjoo]

oh darn. I'm not familiar with macros and macros don't get read in excel for android so I don't think that will work for me. Thanks anyway. Unfortunately I'm limited to the limits of excel mobile.

[Raphaelp]

Put in cell A1 =NOW(). This will update every time the sheet recalculates, so not sure if that works for you.

If you want it formatted as mm/dd/yyyy without the current time also use this: =TEXT(NOW(),"mm/dd/yyyy")

[juntjoo]

Thanks but I don't think so. It would need to be just every time the other cell is edited/changed.

[FDibbins]

the only way that will happen is with VBA. The only other thing closest to that would be =TODAY() - NOW() is not necessary, that adds the time as well - but as said, that is volatile and will update each day

[Aardigspook]

Here's how to put a date stamp in a cell whenever another cell is updated, without VBA.
It comes from this site but there's some sort of display problem with the formulae there which makes the instructions a bit hard to follow, so I've collated them below, plus I've amended the formulae and method slightly to make it a bit easier to follow the calculations and to meet your request for a date-stamp rather than a time-stamp.

Enable Iterative Calculations
This method uses circular references, so you need to enable iterative calculations. Go to the Office button (File button in 2010), click 'Options' then 'Formulas.' At the top, in the 'Calculation Options' section, you'll see an option for 'Enable iterative calculation' - tick/select/check it. The default figures underneath are '100' and '0.001', which you can leave alone. (The site linked above gives a good explanation of what this does and why it's needed, which I'm not going to repeat here.)

Named Ranges
Now, back on your workbook, let's create some Named Ranges, to make it a bit easier to follow the references in the formulae you're about to create. This also means you can move the cells around without having to update the cell references all the time, which is handy.
Named Range 1: You said you want to check when B1 is updated, so let's name that as 'NR_Data' - to name a range, select the cell(s) then click in the cell reference box to the left of the formula box (where it says 'B1' at the moment) and just type in the Name.
(As an aside, it's good practice to preface all your Names with NR or something like that, so that you can easily spot them in your formulae - it also allows you to call a range something like NR_CAB12 whereas CAB12 itself wouldn't be allowed as it's a valid cell reference.)
Named Range 2: Pick any other cell. This will provide a count when the NR_Data cell is changed. So let's Name it 'NR_DataCount'.
Named Range 3: Pick any other cell. This will provide the date when the count in the NR_DataCount cell changes. So we'll Name it 'NR_CountDate'.
Named Range 4: Cell A1, which will give you the final date you want (the date the NR_Data cell was changed) so we'll Name it 'NR_DataDate'.

Formulae
In the NR_DataCount cell, put in this formula:

=IF(NR_Data="","",IF(NR_DataCount="",1&" - "&NR_Data,IF(RIGHT(NR_DataCount,LEN(NR_DataCount)-4)=""&NR_Data,NR_DataCount,MOD(LEFT(NR_DataCount,1)+1,10)&" - "&NR_Data)))

This will return an integer, a hyphen and a copy of the data in the NR_Data cell (for example, '1 - blahblahblah'). The integer will start at '1' and increment to 9, then 0, then 1 again (always remaining a single digit).
The logic behind this is:
If NR_Data is blank, return a blank.
If it's not blank, there's three possible situations:
(1) this is the first data entered - that is, NR_DataCount is blank - so return '1 - NR_Data'
(2) the data hasn't changed - that is the right-hand section of NR_DataCount is equal to what's in NR_Data - so return whatever NR_DataCount is already (don't change it)
(3) the data has changed, so increment the first digit by 1 and update with the current contents of NR_Data.
(For anyone analysing the formula, the ""&NR_Data in the 'LEN(NR_DataCount)-4)=""&NR_Data,' section is to force NR_Data to be evaluated as text.)

In the NR_CountDate cell, put in this formula:

=IF(NR_Data="","",IF(NR_CountDate="",1&" - "&TODAY(),IF(LEFT(NR_CountDate,1)=LEFT(NR_DataCount,1),NR_CountDate,LEFT(NR_DataCount,1)&" - "&TODAY())))

This will return an integer (the same as in NR_DataCount), hyphen and the date. The date will just be a number but don't worry about that for just now.
If you'd rather have a time-stamp than a date-stamp, then change the two instances of TODAY() to NOW().
The logic behind this formula is very similar to the previous formula, except that we can tell the difference between situations (2) and (3) based on whether the integer in this cell matches the integer in NR_DataCount.

In the NR_DataDate cell (A1), put in this formula:

=IF(NR_CountDate="","",VALUE(RIGHT(NR_CountDate,LEN(NR_CountDate)-4)))

This will return the date. Format the cell as a date so it doesn't show as just a number.

Possible problems
If you amend either of the formulae in NR_DataCount or NR_CountDate while there's data in NR_Data, you usually get #VALUE errors appearing. I think this is due to the circular references, which gets Excel confused when combined with the IF statements. If this happens, just delete whatever's in NR_Data and when you enter new data the formulae will work again.
Also, sometimes when you enter data for the first time into the NR_Data cell, the final NR_DataDate cell stays blank. I have NO idea why this happens but it's easy to fix - just select the cell, press F2 then Return (this re-enters the formula without changing anything).


I've attached a file with the above working.
Hope this all does what you want.