Adding a fixed date to a cell

[Guest]

Hi, I want to add the actual date in cell A1 when B1 is populated for the
first time, A2 when B2 etc...
I have tried the formula =today(), but the result is recalculated everytime
the spreedsheet is opened. I have also tried to turn off auto-calculation,
but I need the calculation on new entries. When I calculate manually, every
cell is updated.

I have tried to create a macro that fill the cell as a date instead of a
formula (the command ctrl+shift+ and that works, but I would like this to
happen automatically when the user enter, exit or pick a value in cell.
Does anyone have any suggestions on how to make this happen?

 

[Ron de Bruin]

Hi

You can use code (Event) to do this or manual

You can insert the time like this
CTRL : (colon)

the date like this
CTRL ; (semicolon)

CTRL : (colon) space bar CTRL ; (semicolon)
this will give you both in one cell


Or with VBA

You can do it with the change event of the worksheet
This example will place the date/time in the A column if you change
a cell in the range B1:B20.

Copy the code in the Sheet module

Right click on a sheet tab and choose view code
Paste the code there
Alt-Q to go back to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B1:B20"), Target) Is Nothing Then
Target.Offset(0, -1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
End If
End Sub

 

[Guest]

Hi Ron, and thank you for great help. This solved my problem entirely.