Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
NOW() function that is un-changing
Is there a =NOW() type of function that records the "now" time of when I
first enter it and doesn't update to the current date/time? I'm trying to create a date/time log of certain events that displays the date and time in hh:mm:ss - I only need to be accurate within a few seconds, but the point is that =NOW() wouldn't work. Any suggestions would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
NOW() function that is un-changing
Ctrl-; will enter the date.
Ctrl-: will enter the time. If you want both, you have three options. 1. Use =Now(), but after it's entered, Copy it and Paste SpecialValues 2. Enter Ctrl-; in one cell, Ctrl-: in another, then add them together. 3. Record a macro which will insert the =Now() value in the selected cell. -- Regards, Fred "Cheese" wrote in message ... Is there a =NOW() type of function that records the "now" time of when I first enter it and doesn't update to the current date/time? I'm trying to create a date/time log of certain events that displays the date and time in hh:mm:ss - I only need to be accurate within a few seconds, but the point is that =NOW() wouldn't work. Any suggestions would be appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
NOW() function that is un-changing
Thanks for your help.
How would I configure the macro to enter the =NOW() value into a cell rather than the =NOW() function? "Fred Smith" wrote: Ctrl-; will enter the date. Ctrl-: will enter the time. If you want both, you have three options. 1. Use =Now(), but after it's entered, Copy it and Paste SpecialValues 2. Enter Ctrl-; in one cell, Ctrl-: in another, then add them together. 3. Record a macro which will insert the =Now() value in the selected cell. -- Regards, Fred "Cheese" wrote in message ... Is there a =NOW() type of function that records the "now" time of when I first enter it and doesn't update to the current date/time? I'm trying to create a date/time log of certain events that displays the date and time in hh:mm:ss - I only need to be accurate within a few seconds, but the point is that =NOW() wouldn't work. Any suggestions would be appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
NOW() function that is un-changing
Cheese
I don't know if this will work for you but here is a way that I have used and it works for me. In a cell out of the way, place your "NOW()" function. Highlite the range you want to show your date/time log to be. GotoDataValidation. Under "allow" select LIST, under source select the cell that you placed the "NOW()" function and ok out. Format your range for displaying date/hours/mimutes/seconds, the way you want. When you use the drop down to place the date/time it will not update everytime the sheet re-calculates. Hope this helps you Mike Rogers "Cheese" wrote: Is there a =NOW() type of function that records the "now" time of when I first enter it and doesn't update to the current date/time? I'm trying to create a date/time log of certain events that displays the date and time in hh:mm:ss - I only need to be accurate within a few seconds, but the point is that =NOW() wouldn't work. Any suggestions would be appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
NOW() function that is un-changing
To log into A1, for example:
Sub cheese() Set r = Range("A1") r.NumberFormat = "dd-mmm-yyyy hh:mm:ss" r.Value = Now() End Sub -- Gary''s Student gsnu200711 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
NOW() function that is un-changing
CTRL will give you static Date
SHIFT + CTRL + semi-colon will give you a static time. To get both in the same cell CTRL + ; <space then CTRL + SHIFT + ; Otherwise you could use event code to plunk a static date/time in when a cell is filled. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Target.Value < "" Then With Target.Offset(0, 1) .Value = Format(Now, "dd-mmm-yyyy h:mm:ss") End With End If End If enditall: Application.EnableEvents = True End Sub When you enter something in column A the Date/Time is entered in column B. If you don't want to be able to have the date/time update when editing column A later make these changes. If Target.Value < "" _ And Target.Offset(0, 1).Value = "" Then This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Sun, 18 Mar 2007 15:45:05 -0700, Cheese wrote: Is there a =NOW() type of function that records the "now" time of when I first enter it and doesn't update to the current date/time? I'm trying to create a date/time log of certain events that displays the date and time in hh:mm:ss - I only need to be accurate within a few seconds, but the point is that =NOW() wouldn't work. Any suggestions would be appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
NOW() function that is un-changing
Cheese, like this,
Sub Enter_Now() ActiveCell = Now 'if you only want to show time ActiveCell.NumberFormat = "h:mm:ss" End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Cheese" wrote in message ... Thanks for your help. How would I configure the macro to enter the =NOW() value into a cell rather than the =NOW() function? "Fred Smith" wrote: Ctrl-; will enter the date. Ctrl-: will enter the time. If you want both, you have three options. 1. Use =Now(), but after it's entered, Copy it and Paste SpecialValues 2. Enter Ctrl-; in one cell, Ctrl-: in another, then add them together. 3. Record a macro which will insert the =Now() value in the selected cell. -- Regards, Fred "Cheese" wrote in message ... Is there a =NOW() type of function that records the "now" time of when I first enter it and doesn't update to the current date/time? I'm trying to create a date/time log of certain events that displays the date and time in hh:mm:ss - I only need to be accurate within a few seconds, but the point is that =NOW() wouldn't work. Any suggestions would be appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
NOW() function that is un-changing
Sub TimeStamp()
ActiveCell.Value = Time ActiveCell.EntireColumn.AutoFit End Sub Vaya con Dios, Chuck, CABGx3 "Cheese" wrote in message ... Thanks for your help. How would I configure the macro to enter the =NOW() value into a cell rather than the =NOW() function? "Fred Smith" wrote: Ctrl-; will enter the date. Ctrl-: will enter the time. If you want both, you have three options. 1. Use =Now(), but after it's entered, Copy it and Paste SpecialValues 2. Enter Ctrl-; in one cell, Ctrl-: in another, then add them together. 3. Record a macro which will insert the =Now() value in the selected cell. -- Regards, Fred "Cheese" wrote in message ... Is there a =NOW() type of function that records the "now" time of when I first enter it and doesn't update to the current date/time? I'm trying to create a date/time log of certain events that displays the date and time in hh:mm:ss - I only need to be accurate within a few seconds, but the point is that =NOW() wouldn't work. Any suggestions would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index function and changing criteria help. | Excel Worksheet Functions | |||
Changing other fields using IF Function!? | Excel Worksheet Functions | |||
Changing worksheet cells from within a function | Setting up and Configuration of Excel | |||
Changing cell formatting with IF function | Excel Worksheet Functions | |||
Can I use TODAY Function in formula without it changing the next . | Excel Discussion (Misc queries) |