Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Adding a control button to insert a date in the active cell. | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Extract date from cell | Excel Worksheet Functions |