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

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).

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

Try this one-line macro:

Sub time_it()
Selection.Value = Now
End Sub

you can assign a shortcut like CNTRL-e to it.
--
Gary''s Student - gsnu200720


"gwm" wrote:

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).

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

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


On Wed, 9 May 2007 12:49:01 -0700, gwm wrote:

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).

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

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

Gord Dibben MS Excel MVP

On Wed, 09 May 2007 18:31:50 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


On Wed, 9 May 2007 12:49:01 -0700, gwm wrote:

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).

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

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 wrote:

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

Gord Dibben MS Excel MVP

On Wed, 09 May 2007 18:31:50 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


On Wed, 9 May 2007 12:49:01 -0700, gwm wrote:

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).

--

Dave Peterson

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

Thanks Dave

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


Gord

On Wed, 09 May 2007 21:35:35 -0500, Dave Peterson
wrote:

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 wrote:

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

Gord Dibben MS Excel MVP

On Wed, 09 May 2007 18:31:50 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


On Wed, 9 May 2007 12:49:01 -0700, gwm wrote:

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).

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

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

Gord Dibben wrote:

Thanks Dave

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

Gord

On Wed, 09 May 2007 21:35:35 -0500, Dave Peterson
wrote:

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 wrote:

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

Gord Dibben MS Excel MVP

On Wed, 09 May 2007 18:31:50 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


On Wed, 9 May 2007 12:49:01 -0700, gwm wrote:

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).

--

Dave Peterson

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

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

"Gord Dibben" wrote:

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


On Wed, 9 May 2007 12:49:01 -0700, gwm wrote:

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).

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

Hi MarySue

just remove the AM/PM

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



--
Regards

Roger Govier


"MarySue" wrote in message
...
How do you format for a 24 hour clock, so I don't have to use AM/PM?

"Gord Dibben" wrote:

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


On Wed, 9 May 2007 12:49:01 -0700, gwm
wrote:

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).

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

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


Gord

On Thu, 12 Jul 2007 08:16:00 -0700, MarySue
wrote:

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

"Gord Dibben" wrote:

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


On Wed, 9 May 2007 12:49:01 -0700, gwm wrote:

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).

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

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

On Thu, 12 Jul 2007 17:02:20 +0100, "Roger Govier"
wrote:

Hi MarySue

just remove the AM/PM

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

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

Hi Gord

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

--
Regards

Roger Govier


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 12 Jul 2007 17:02:20 +0100, "Roger Govier"
wrote:

Hi MarySue

just remove the AM/PM

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

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

Hi Gord

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

--
Regards

Roger Govier


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
My mistook it seems.

Works fine currently. Did not earlier.

Don't have any idea<g


Gord

On Thu, 12 Jul 2007 18:54:50 +0100, "Roger Govier"
wrote:

Hi Gord

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

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

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

On Thu, 12 Jul 2007 21:12:17 +0100, "Roger Govier"
wrote:

Hi Gord

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

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

My mistook it seems.

Works fine currently. Did not earlier.

Don't have any idea<g


Gord

On Thu, 12 Jul 2007 18:54:50 +0100, "Roger Govier"
wrote:

Hi Gord

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