Is there a way to make a cell create a time stamp when you want it to and then stick to that date/time for ever? So when you open the file the next day the time stamp doesn't update to the current date/time? Thanks.

Hi,

Am Wed, 15 Apr 2015 11:07:07 -0700 (PDT) schrieb :

Is there a way to make a cell create a time stamp when you want it to and then stick to that date/time for ever? So when you open the file the next day the time stamp doesn't update to the current date/time? Thanks.

you have to do it with VBA.
For example:

Sub TS()
With Range("A1")
.Value = Now
.NumberFormat = "MM/DD/YYYY h:mm:ss"
End With
End Sub

Modify the numberformat to your expected format.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Wednesday, April 15, 2015 at 2:10:58 PM UTC-4, Claus Busch wrote:
Hi,

Am Wed, 15 Apr 2015 11:07:07 -0700 (PDT) schrieb :

Is there a way to make a cell create a time stamp when you want it to and then stick to that date/time for ever? So when you open the file the next day the time stamp doesn't update to the current date/time? Thanks.

you have to do it with VBA.
For example:

Sub TS()
With Range("A1")
.Value = Now
.NumberFormat = "MM/DD/YYYY h:mm:ss"
End With
End Sub

Modify the numberformat to your expected format.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks for the response, but I wasn't following. Below is my exact situation. Can you advise me on how to do it?

Cell L115 is blank. I would like cell M115 to have the time stamp. If I type an x in cell L115, I would like cell M115 to show 04/15/15. Even if I open the file tomorrow I would still want M115 to show 4/15/15. Thanks.

Hi,

Am Wed, 15 Apr 2015 11:19:05 -0700 (PDT) schrieb :

Cell L115 is blank. I would like cell M115 to have the time stamp. If I type an x in cell L115, I would like cell M115 to show 04/15/15. Even if I open the file tomorrow I would still want M115 to show 4/15/15. Thanks.

right click on the sheet tab = Show Code and insert following code into
the code window of the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$L$115" Then Exit Sub

With Target.Offset(, 1)
If Target = "x" Then .Value = Date
.NumberFormat = "MM/DD/YY"
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Wednesday, April 15, 2015 at 2:26:06 PM UTC-4, Claus Busch wrote:
Hi,

Am Wed, 15 Apr 2015 11:19:05 -0700 (PDT) schrieb :

Cell L115 is blank. I would like cell M115 to have the time stamp. If I type an x in cell L115, I would like cell M115 to show 04/15/15. Even if I open the file tomorrow I would still want M115 to show 4/15/15. Thanks.

right click on the sheet tab = Show Code and insert following code into
the code window of the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$L$115" Then Exit Sub

With Target.Offset(, 1)
If Target = "x" Then .Value = Date
.NumberFormat = "MM/DD/YY"
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional



Ok, thanks! I understand now and am getting it to work. However, I thought this would be more of a formula than a format. Let's say I have multiple tabs that this would apply to where the X's would be all done in column L and I would need the time stamp to show in Column M in the row. How would that get applied to the entire worksheet? Thanks for your help.

Hi,

Am Wed, 15 Apr 2015 11:54:56 -0700 (PDT) schrieb :

Ok, thanks! I understand now and am getting it to work. However, I thought this would be more of a formula than a format. Let's say I have multiple tabs that this would apply to where the X's would be all done in column L and I would need the time stamp to show in Column M in the row. How would that get applied to the entire worksheet? Thanks for your help.

Alt+F11 = Double click on "ThisWorkbook" and insert following code
into the code window:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address < "$L$115" Then Exit Sub

With Target.Offset(, 1)
If LCase(Target) = "x" Then .Value = Date
.NumberFormat = "MM/DD/YY"
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional