Now function

[Guest]

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?

 

[Peo Sjoblom]

Copy and paste special as values?
Here are some options


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



--


Regards,


Peo Sjoblom

 

[Guest]

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-applications-and-spreadsheet-programming

 

[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


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

You could set up the worksheet to calculate manually. This would preclude

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

 

[Guest]

Rick;

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

Thanks

Frank Pytel

 

[David McRitchie]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[David McRitchie]

The answer is No, because using NOW Worksheet Function
will not be a constant, it will be recalculated each time that a
recalculation is done, which will be done at least when worksheet
is opened. You are thinking VB and mixing your answer into
other incorrect worksheet function responses.
See my answer earlier (in subthread below).

 

[Peo Sjoblom]

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

 

[Rick Rothstein \(MVP - VB\)]

Look at my original post again... you type =NOW(), but you key-in F9 to
"enter" it; you do **not** use the Enter key to do so... this places the
'now' value in the cell as a constant, down to the seconds.

Rick

 

[David McRitchie]

Learn something new everyday, and I doubled checked just to
see if that was in Excel 2002 as well as Excel 2007 and it was.
Thanks for pointing that out, that use of F9 is certainly not
intuitive but I doubt that it would cause accidental problems.

Incidentally, often such entries can be automated to create
the timestamp when the adjacent cell is updated, so much
less work using an Change Event macro if that sheet gets
a lot of new entries. The user would have to change the code
but something along the lines of
http://www.mvps.org/dmcritchie/excel/event.htm#datetimestamp

 

[Rick Rothstein \(MVP - VB\)]

Learn something new everyday, and I doubled checked just to see if that

was in Excel 2002 as well as Excel 2007 and it was.
Thanks for pointing that out, that use of F9 is certainly not intuitive
but I doubt that it would cause accidental problems.

It was a new one for me too! Remember, this is Teethless mama's discovery,
although he doesn't mention how long ago it was that he discovered it.

Incidentally, often such entries can be automated to create
the timestamp when the adjacent cell is updated, so much
less work using an Change Event macro if that sheet gets
a lot of new entries. The user would have to change the code
but something along the lines of
http://www.mvps.org/dmcritchie/excel/event.htm#datetimestamp

True, but as Harlan has pointed out to me a couple of times in the past,
macro solutions are not always desirable.

Rick

 

[David McRitchie]

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)

 

[Peo Sjoblom]

How do you know TM is a he and not a she and shouldn't it be Toothless Mama?

Anyway it was discovered earlier, I probably saw it here back in 2000
something.

I use it all the time and I have for a long time, if I want to hardcode
let's say a grade setup in vlookup I just create a table first, then I put
in the grades and their values and what looks like.


=VLOOKUP(A1,F3:G8,2,0)

will look like

=VLOOKUP(A1,{0,"F";55,"D";65,"C";75,"B";85,"A+";95,"A+"},2,0)


after I have highlighted F3:G8 and pressed F9, that way a poster can just
copy the formula directly and just change A1
Same when I build complicated formulas where I can select parts of the
formula to find out where the problem is, that is much easier than using the
built in auditing tool. The way out is to use the escape key otherwise it
will be fixed


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

How do you know TM is a he and not a she

I don't know for sure... it is just a "gut feeling".

and shouldn't it be Toothless Mama?

Anyway it was discovered earlier, I probably saw it here back in 2000
something.

I use it all the time and I have for a long time, if I want to hardcode
let's say a grade setup in vlookup I just create a table first, then I put
in the grades and their values and what looks like.


=VLOOKUP(A1,F3:G8,2,0)

will look like

=VLOOKUP(A1,{0,"F";55,"D";65,"C";75,"B";85,"A+";95,"A+"},2,0)


after I have highlighted F3:G8 and pressed F9, that way a poster can just
copy the formula directly and just change A1
Same when I build complicated formulas where I can select parts of the
formula to find out where the problem is, that is much easier than using
the built in auditing tool. The way out is to use the escape key otherwise
it will be fixed

Yes, I use F9 for that too (it is great for looking at array results also).
For some reason, I didn't link the two uses of F9 in my mind, probably
because NOW() seems different from a "normal" function (no arguments, I
guess is why).

Rick