I need to use a formula that enters a static time in a cell based on the contents of another cell (the values will be 0 and 1). The NOW() function enters a dynamic time that changes each time the worksheet is recalculated. I need to enter the time as if I used the "Ctrl+Shift+:" keyboard strokes. I have tried several different things without success including a VB script that I found on a user site. In this particular application, using the "Paste Special" command in a macro would be too cumbersome. I can't believe that there is not a way to enter a static time using a formula. It looks like MicroSoft missed one here. Any suggestions??

Gino Tortelle wrote:
I need to use a formula that enters a static time in a cell based on
the contents of another cell (the values will be 0 and 1). The NOW()
function enters a dynamic time that changes each time the worksheet
is recalculated. I need to enter the time as if I used the
"Ctrl+Shift+:" keyboard strokes. I have tried several different
things without success including a VB script that I found on a user
site. In this particular application, using the "Paste Special"
command in a macro would be too cumbersome. I can't believe that
there is not a way to enter a static time using a formula. It looks
like MicroSoft missed one here. Any suggestions??

Hi Gino
have a look at
http://www.mcgimpsey.com/excel/timestamp.html
you may use the worksheet_change procedure to achieve your goal

Frank

Assume if an entry is made in column B, you want a timestamp in Column A.

right click on the sheet tab and select View code. Paste in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 2 Then
Application.EnableEvents = False
If Not IsEmpty(Target) Then
Cells(Target.Row, 1).Value = Now
Cells(Target.Row, 1).NumberFormat = "mm/dd/yyyy hh:mm"
Else
Cells(Target.Row, 1).ClearContents
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Gino Tortelle" wrote in message
...
I need to use a formula that enters a static time in a cell based on the
contents of another cell (the values will be 0 and 1). The NOW() function
enters a dynamic time that changes each time the worksheet is recalculated.
I need to enter the time as if I used the "Ctrl+Shift+:" keyboard strokes. I
have tried several different things without success including a VB script
that I found on a user site. In this particular application, using the
"Paste Special" command in a macro would be too cumbersome. I can't believe
that there is not a way to enter a static time using a formula. It looks
like MicroSoft missed one here. Any suggestions??