Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 29th 09, 10:32 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 5
Default Get current time NOW() but stop it updating

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers



  #2   Report Post  
Old September 29th 09, 10:38 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,646
Default Get current time NOW() but stop it updating

You can do that via a VBA event macro, but first you have to specify
1. in what cell do you want to enter NOW
2. what is the event (e.g. filling in a particular cell) triggering time
stamping.

Regards,
Stefi

„goneil” ezt *rta:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers


  #3   Report Post  
Old September 29th 09, 10:46 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 833
Default Get current time NOW() but stop it updating

Ctrl plus the shift key plus the ; (semi-colon) (hit all 3 keys at the same
time) will return the current time in the cell and it will not be updated.

Please hit Yes if my comments have helped.

Thanks!

"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers


  #4   Report Post  
Old September 29th 09, 10:48 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 833
Default Get current time NOW() but stop it updating

Ctrl and the ; (semi colon) hit simultaneously will return the current date
to a cell and it will not be updated.

Please hit Yes if my comments have helped.

Thanks!

"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers


  #5   Report Post  
Old September 29th 09, 10:51 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 833
Default Get current time NOW() but stop it updating

From MS documentation:-

to insert the current date and time, press CTRL+; (semi-colon), then press
SPACE, and then press CTRL+SHIFT+; (semi-colon).

Please hit Yes if my comments have helped.

Thanks!



"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers




  #6   Report Post  
Old September 29th 09, 10:53 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default Get current time NOW() but stop it updating

To record the timestamp manually you can hit Ctrl+; followed by space bar and
followed by Ctrl+Shift+;

To record the timestamp automatically you cannot use a formula. You will
have to use a VBA solution. Select the sheet tab which you want to work with.
Right click the sheet tab and click on 'View Code'. This will launch VBE.
Paste the below code to the right blank portion. Get back to to workbook and
try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers


  #7   Report Post  
Old September 29th 09, 11:20 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,646
Default Get current time NOW() but stop it updating

Yes, but this solution doesn't meet the OP's requirement "without the data
entry operator having to do anything"!

Stefi


„trip_to_tokyo” ezt *rta:

Ctrl plus the shift key plus the ; (semi-colon) (hit all 3 keys at the same
time) will return the current time in the cell and it will not be updated.

Please hit Yes if my comments have helped.

Thanks!

"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers


  #8   Report Post  
Old September 29th 09, 12:29 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 506
Default Get current time NOW() but stop it updating

Try this.

Past the below code in Excel VBA Module and assign shortcut key for that.

Press ALT+F11 and go to Insert and click Module. And paste the below code.

Sub DateandTime()
ActiveCell = Now()
End Sub

After pasting the code close the VBA and press ALT+F8, the DateandTime Macro
will appear. Just select it and select Options and give your desired
keyboard button as shortcut key to run the macro.

If you want to show the cells including seconds just select the cells and
Goto Format and select custom and paste this format [$-409]m/d/yy h:mm:ss
AM/PM;@

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------




"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers


  #9   Report Post  
Old September 29th 09, 12:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 506
Default Get current time NOW() but stop it updating

My post will not solve your problem, since it needs to be done manually. So,
Pls ignore my post.

--------------------
(Ms-Exl-Learner)
--------------------



"Ms-Exl-Learner" wrote:

Try this.

Past the below code in Excel VBA Module and assign shortcut key for that.

Press ALT+F11 and go to Insert and click Module. And paste the below code.

Sub DateandTime()
ActiveCell = Now()
End Sub

After pasting the code close the VBA and press ALT+F8, the DateandTime Macro
will appear. Just select it and select Options and give your desired
keyboard button as shortcut key to run the macro.

If you want to show the cells including seconds just select the cells and
Goto Format and select custom and paste this format [$-409]m/d/yy h:mm:ss
AM/PM;@

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------




"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers


  #10   Report Post  
Old September 30th 09, 03:21 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 5
Default Get current time NOW() but stop it updating

Hi Stefi,

Thank you for your response, much appreciated. I think this may not be
possible and perhaps thats why I cant figure it out on my own (I also don't
know VBA) or with community help.

I have a spreadsheet that I want to record how much time I spend on each
call I make or receive. I need to do it efficiently because I may only spend
1 or 2 minutes on some calls or less but rounded up to a minimum 1 minute.

I want to simply enter the Duration of the call and Nothing Else:

1. Enter call Duration hh mm into separate cells, then populate other cells
by:
2. Get the Current Time
3. Subtract the call duration from Current Time
4. And that becomes my Start Time.

The problem is that the end time keeps updating because it gets the current
timestamp from NOW().

A B C D
Duration -----------Time---------
Hours Mins Start End
1 20 9:40 11:00
(NOW - Duration) (NOW)

Any ideas Stefi?
cheers
George

"Stefi" wrote:

You can do that via a VBA event macro, but first you have to specify
1. in what cell do you want to enter NOW
2. what is the event (e.g. filling in a particular cell) triggering time
stamping.

Regards,
Stefi

„goneil” ezt *rta:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers




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
Is there a formula which returns the current (updating) year? George Parrog Excel Discussion (Misc queries) 2 April 13th 09 09:54 AM
Updating with current date Gloria Lewis Excel Discussion (Misc queries) 0 February 26th 08 08:55 PM
How to Input date & Time [NOW()], then stop it updating? jonathandr Excel Worksheet Functions 3 October 23rd 06 05:41 PM
Having the current time inserted w/o updating the current time sherobot Excel Worksheet Functions 2 October 2nd 06 05:05 PM
Can I automatically enter the current date or current time into a Ben New Users to Excel 7 October 19th 05 03:38 PM


All times are GMT +1. The time now is 11:06 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017