ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   NOW() function that is un-changing (https://www.excelbanter.com/excel-discussion-misc-queries/135353-now-function-un-changing.html)

Cheese March 18th 07 10:45 PM

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.

Fred Smith March 18th 07 10:59 PM

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.




Cheese March 18th 07 11:07 PM

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.





Mike Rogers March 18th 07 11:10 PM

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.


Gary''s Student March 18th 07 11:16 PM

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


Gord Dibben March 18th 07 11:44 PM

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.



Paul B March 19th 07 12:22 AM

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.







CLR March 19th 07 12:51 AM

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.








All times are GMT +1. The time now is 08:08 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com