I would like to use the now function to record the current system time with
seconds, but I do not want this function to update when I type another
instance of the now function. I need seconds so using ctrl+shift+; does not
work. Is there a short cut or syntax that I can use to keep the now function
static?

Copy and paste special as values?
Here are some options


http://www.mcgimpsey.com/excel/timestamp.html



--


Regards,


Peo Sjoblom




"Albie" wrote in message
...
I would like to use the now function to record the current system time with
seconds, but I do not want this function to update when I type another
instance of the now function. I need seconds so using ctrl+shift+; does
not
work. Is there a short cut or syntax that I can use to keep the now
function
static?

Albie;

You could set up the worksheet to calculate manually. This would preclude
any updates until you choose. Unfortunately it will update when you
calculate. You could also try parsing. Choose =a1 and set to text. Try
parsing that cell.

God Bless

Frank Pytel

http://groups.google.com/group/excel...et-programming

"Albie" wrote:

I would like to use the now function to record the current system time with
seconds, but I do not want this function to update when I type another
instance of the now function. I need seconds so using ctrl+shift+; does not
work. Is there a short cut or syntax that I can use to keep the now function
static?

You could set up the worksheet to calculate manually. This would preclude
any updates until you choose. Unfortunately it will update when you
calculate. You could also try parsing. Choose =a1 and set to text. Try
parsing that cell.


What do you mean by parsing? If you link to another cell that will change
the linked cell will change as well


--


Regards,


Peo Sjoblom

You could set up the worksheet to calculate manually. This would preclude
any updates until you choose. Unfortunately it will update when you
calculate. You could also try parsing. Choose =a1 and set to text. Try
parsing that cell.


What do you mean by parsing? If you link to another cell that will change
the linked cell will change as well

Just to update you and Frank, this question was asked over in the
worksheet.function group also and Teethless mama gave this response...

=NOW() (press F9 instead of ENTER)

Rick

Rick;

Thanks. What does that do. Enter the function and calculate now and once only?

Thanks

Frank Pytel

"Rick Rothstein (MVP - VB)" wrote:

You could set up the worksheet to calculate manually. This would preclude
any updates until you choose. Unfortunately it will update when you
calculate. You could also try parsing. Choose =a1 and set to text. Try
parsing that cell.


What do you mean by parsing? If you link to another cell that will change
the linked cell will change as well

Just to update you and Frank, this question was asked over in the
worksheet.function group also and Teethless mama gave this response...

=NOW() (press F9 instead of ENTER)

Rick

Remember, this was Teethless mama's posting that I repeated, not my own. But
to answer your question, yes, it replaces the =NOW() with the exact
date/time (down to seconds), although you might have to Custom Format
with...

mm/dd/yyyy h:mm:ss AM/PM

in order to see it. Just try it and look at the formula bar for the cell
afterwards.

Rick


"Frank Pytel" wrote in message
...
Rick;

Thanks. What does that do. Enter the function and calculate now and once
only?

Thanks

Frank Pytel

"Rick Rothstein (MVP - VB)" wrote:

You could set up the worksheet to calculate manually. This would
preclude
any updates until you choose. Unfortunately it will update when you
calculate. You could also try parsing. Choose =a1 and set to text. Try
parsing that cell.


What do you mean by parsing? If you link to another cell that will
change
the linked cell will change as well

Just to update you and Frank, this question was asked over in the
worksheet.function group also and Teethless mama gave this response...

=NOW() (press F9 instead of ENTER)

Rick

Hi Albie,
You can use Ctrl+; (space) Ctrl+Shift+:
but that will take you at least a second to type, and you sound
like you want to do this often and quickly.

You cannot use a Worksheet Function to do that, not one that
I'd trust anyway. Each time you hit F9 the value would change
so it would not be a constant. Would suggest that you use an
Event Macro say double-click on a cell.

You can use an Event macro, which is installed for the specific
worksheet you are on. Right-click on the sheet tab, then "View Code",
then paste the following code after your Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Cancel = True
Target.Value = Now 'only down to seconds
Target.NumberFormat = "yyyy-mm-dd hh:mm:ss"
End Sub

If you only wanted Time then use:
Target.Value = Now Mod 1
Target.NumberFomat = "hh:mm:ss"

It would be more efficient to format the entire column
beforehand rather than formatting individually within
the macro.

If you want to learn more about Event macros see
http://www.mvps.org/dmcritchie/excel/event.htm

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Albie" wrote in message ...
I would like to use the now function to record the current system time with
seconds, but I do not want this function to update when I type another
instance of the now function. I need seconds so using ctrl+shift+; does not
work. Is there a short cut or syntax that I can use to keep the now function
static?

"David McRitchie" wrote in message
...
Hi Albie,
You can use Ctrl+; (space) Ctrl+Shift+:
but that will take you at least a second to type, and you sound
like you want to do this often and quickly.


But the OP needed seconds and the keyboard shortcuts return hours and
minutes not seconds


--


Regards,


Peo Sjoblom

Right, after discovering that VBA TIME function and
CTRL+SHIFT+: only provided down to minutes, I stuck
that paragraph in at the top like an idiot afterwards.

And hadn't noticed the Excel trick use of F9 instead of enter
to convert to a constant. Thanks Peo and Rich.

Still not clear if the poster really wanted just time or datetimestamp;
If only the time was wanted the formula would be =MOD(NOW(),1)
--
David


"Peo Sjoblom" wrote in message ...

"David McRitchie" wrote in message
...
Hi Albie,
You can use Ctrl+; (space) Ctrl+Shift+:
but that will take you at least a second to type, and you sound
like you want to do this often and quickly.


But the OP needed seconds and the keyboard shortcuts return hours and
minutes not seconds


--


Regards,


Peo Sjoblom