Adding a fixed date to a cell
 

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?

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Erik Jahre" wrote in message ...
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?

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

"Erik Jahre" wrote:

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?