Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 18th 07, 10:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 7
Default 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   Report Post  
Old March 18th 07, 10:59 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 623
Default 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   Report Post  
Old March 18th 07, 11:07 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 7
Default 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   Report Post  
Old March 18th 07, 11:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 194
Default 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   Report Post  
Old March 18th 07, 11:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default 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   Report Post  
Old March 18th 07, 11:44 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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   Report Post  
Old March 19th 07, 12:22 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 709
Default 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   Report Post  
Old March 19th 07, 12:51 AM posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 594
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index function and changing criteria help. [email protected] Excel Worksheet Functions 5 August 22nd 06 07:37 AM
Changing other fields using IF Function!? Neo1 Excel Worksheet Functions 4 January 17th 06 09:09 AM
Changing worksheet cells from within a function James4U2enjoy Setting up and Configuration of Excel 1 October 14th 05 02:16 PM
Changing cell formatting with IF function Bruise Excel Worksheet Functions 6 May 21st 05 11:40 PM
Can I use TODAY Function in formula without it changing the next . Tucson Guy Excel Discussion (Misc queries) 1 December 19th 04 09:47 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017