Static Time Entry
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??
|
Static Time Entry
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 |
Static Time Entry
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?? |
All times are GMT +1. The time now is 02:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com