Excel: How to insert current time (static) to the nearest second?

[Guest]

When inserting the current time you press CTRL + SHIFT + ; and it displays it
in hours and minutes only. By formatting the cell to display seconds, it will
give 00 seconds rather than more specific time to the nearest second. Can
this function be adjusted to be more precise and display time to the nearest
second rather than just the nearest minute? Note: The "=NOW()" function does
display time to the nearest second, but I cannot use it in this case because
it is not static (i.e. updates).

 

[Guest]

Try this one-line macro:

Sub time_it()
Selection.Value = Now
End Sub

you can assign a shortcut like CNTRL-e to it.

 

[Gord Dibben]

That method will give you the static time but only to the minute.

To the second, use this macro and assign it to the shortcut combo or a button.

Sub NOWTIME()
ActiveCell.Value = Format(Now(), "h:mm:ss AM/PM")
End Sub



Gord Dibben MS Excel MVP

 

[Gord Dibben]

Note: you can't assign it to CTRL + SHIFT + ;

Gord Dibben MS Excel MVP

 

[Dave Peterson]

Option Explicit
Sub Auto_Open()
Application.OnKey "+^:", "'" & ThisWorkbook.Name & "'!Nowtime"
End Sub
Sub Auto_Close()
Application.OnKey "+^:"
End Sub
Sub NOWTIME()
ActiveCell.Value = Format(Now(), "h:mm:ss AM/PM")
End Sub

My version of NowTime looks more like:

Sub NOWTIME()
On Error Resume Next
With Selection
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
If Err.Number <> 0 Then
Beep
End If
End Sub

So I can fill multiple cells.

 

[Gord Dibben]

Thanks Dave

I should know better than to say "you can't"<g>


Gord

 

[Dave Peterson]

That phrase scares the heck out of me, too--just based on the followup posts
proving me wrong!

 

[Guest]

How do you format for a 24 hour clock, so I don't have to use AM/PM?

 

[Roger Govier]

Hi MarySue

just remove the AM/PM

ActiveCell.Value = Format(Now(), "h:mm:ss")

 

[Gord Dibben]

ActiveCell.Value = Format(Now(), "hh:mm:ss;@")


Gord

 

[Gord Dibben]

Roger

That doesn't return 14:23:23

It returns 2:23:23

Need a custom format of "h:mm:ss;@" or "hh:mm:ss;@"


Gord

 

[Roger Govier]

Hi Gord

in my immediate window
?Format(Now(), "h:mm:ss")
18:53:22

 

[Roger Govier]

Hi Gord

Perhaps it's because you are already in tomorrow!!<bg>
Take a look at the date stamp.

 

[Gord Dibben]

Awww geez

I was dorking about with my Date settings in control panel to test and forgot to
change back.

I've been posting tomorrow all afternoon.

Fixed now.

Thanks for pointing that out.

It's these new drugs. That's my story and I'm sticking with it<g>


Gord

 

[Gord Dibben]

My mistook it seems.

Works fine currently. Did not earlier.

Don't have any idea<g>


Gord