Static time and date stamp

**eric11** · 03-02-2007, 05:16 PM

Hi
I would like to have a static time and date stamp automatically enter into two adjacent cells after a person has entered their intials in a cell.

Using an an "if then else" where it checks to see if initials have been entered then using now() will not work as it is always up dates when the sheet is recalculated. I dont think you can use ctrl+; in an =if( condition,true,false) statement.
Any ideas.
Thanks

**mudraker** · 03-02-2007, 05:38 PM

You can not get a static date & time in a cell using formulas you will need to use a change event macro

'To install macro to correct location

Copy this macro
GoTo Excel
Select sheet this is to appy to
Right Click on Sheet Name Tab > select View Code
Past macro into the Worksheet Module displayed

Change the column numbers in red to suit

Please Login or Register  to view this content.

**eric11** · 03-02-2007, 05:51 PM

Thanks
I will try.
My knowlege of VBA is limited, but if I undertsand the code it will trigger the event when data is entered in col 3 and post date and time in cols 4 and 5. So will this update each time data is entered in col 3?
Unfortunatley I did not mention orginally, there are 24 rows and it's at each row when a person enters their initials it updates with time and date for that row.

Thanks

**eric11** · 03-02-2007, 06:05 PM

Thanks the macro works great

**mudraker** · 03-02-2007, 06:35 PM

Do you have it working as required or do you need it to be modified to work on rows or a range of cells.

If yes post details of rows or cell ranges

**eric11** · 03-02-2007, 07:59 PM

Hi
Yes, the macro works as needed on a cells. Thanks.
The only thing is I protected the range of cells so they can't be changed by the user but that also prevents the macro from updating. Is it easy to temporarily unlock them in the macro, update and lock them again?
Thanks

**mudraker** · 03-02-2007, 09:41 PM

This will work if you have not used a password to protect the sheet

Please Login or Register  to view this content.

**ynks4ever1** · 03-06-2007, 01:14 PM

mudraker,
This is exactly what I need it as well. I have a quick question though... how to modify the macro to only update the same cell (with the date stamp) if someone modifies the sheet?
Thanks a lot

**mudraker** · 03-06-2007, 06:18 PM

Use the previous enclosed macroand change
Cells(Target.Row, 4) = Now()
to

Please Login or Register  to view this content.

Change Red cell reference (a1) to suit your needs

**chrisvae** · 01-17-2014, 07:48 AM

This solution is very usefull for me. Thanks.

Originally Posted by mudraker

You can not get a static date & time in a cell using formulas you will need to use a change event macro

'To install macro to correct location

Copy this macro
GoTo Excel
Select sheet this is to appy to
Right Click on Sheet Name Tab > select View Code
Past macro into the Worksheet Module displayed

Change the column numbers in red to suit

Please Login or Register  to view this content.

**saadarslan** · 10-01-2015, 01:27 PM

Hi,
I am trying to update a column with date stamps if the corresponding cell in first column is updated with ABC then time stamp the corresponding cell in column 2 and if the first column cell is updated with XYZ update column 3 corresponding cell with time stamp, also these dates need to be static once entered they shouldn't change unless the cell is wiped clean purposely.