Enter current time into cell hh:mm:ss

rglasunow · #1 06-24-2005, 06:26 PM

I am trying to create a macro that records the current time off the clock on my computer. I am aware of the short cut (ctrl+shift+

. However, this does not go into the actual seconds. Is there a way to record the seconds as well?

**dominicb** · #2 06-24-2005, 06:40 PM

Good evening rglasunow

You would be better off using the =NOW() function, the only problem with this is that it will alter every time the sheet is recalculated. The code below will call the NOW() function, format the cell appropriately and then changes the cell contents from a formula to a value.

Sub TimeStamp()
ActiveCell.FormulaR1C1 = "=NOW()"
With Selection
.NumberFormat = "h:mm:ss;@"
.Formula = .Value
End With
End Sub

HTH

DominicB

rglasunow · #3 06-24-2005, 06:45 PM

thanks man!! That worked perfect!!!

**dominicb** · #4 06-24-2005, 07:12 PM

Hi rglasunow

Glad to help. Appreciate the feedback.

DominicB

#5 06-24-2005, 08:05 PM

Why not do it in one go?

Sub TimeStamp()
ActiveCell.Value = Format(Time,"h:mm:ss;@")
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"dominicb" <dominicb.1r5l6e_1119654307.1749@excelforum-nospam.com> wrote in
message news:dominicb.1r5l6e_1119654307.1749@excelforum-nospam.com...
>
> Good evening rglasunow
>
> You would be better off using the =NOW() function, the only problem
> with this is that it will alter every time the sheet is recalculated.
> The code below will call the NOW() function, format the cell
> appropriately and then changes the cell contents from a formula to a
> value.
>
> Sub TimeStamp()
> ActiveCell.FormulaR1C1 = "=NOW()"
> With Selection
> NumberFormat = "h:mm:ss;@"
> Formula = .Value
> End With
> End Sub
>
> HTH
>
> DominicB
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile:
http://www.excelforum.com/member.php...o&userid=18932
> View this thread: http://www.excelforum.com/showthread...hreadid=382135
>

#6 06-24-2005, 08:05 PM

One way:

Public Sub InsertTime()
With ActiveCell
.Value = Time
.NumberFormat = "hh:mm:ss"
End With
End Sub

In article <rglasunow.1r5l6c_1119654306.1858@excelforum-nospam.com>,
rglasunow <rglasunow.1r5l6c_1119654306.1858@excelforum-nospam.com>
wrote:

> I am trying to create a macro that records the current time off the
> clock on my computer. I am aware of the short cut (ctrl+shift+

.
> However, this does not go into the actual seconds. Is there a way to
> record the seconds as well?

#7 06-24-2005, 08:05 PM

This really doesn't do exactly what the OP says s/he wanted - since
NOW() returns both the date and the time (though it may be acceptable to
the OP).

Entering the time using Activecell, but then using Selection for

.Formula = .Value

is dangerous - if the user has selected multiple cells, it will reformat
and wipe out any formulae in that selection. Far better to stick with
ActiveCell.

Also, if the OP want's both date and time, there's no need to use XL's
NOW() function, use VBA's Now method instead:

Public Sub TimeStamp()
With ActiveCell
.Value = Now
.NumberFormat = "h:mm:ss"
End With
End Sub

In article <dominicb.1r5l6e_1119654307.1749@excelforum-nospam.com>,
dominicb <dominicb.1r5l6e_1119654307.1749@excelforum-nospam.com>
wrote:

> Good evening rglasunow
>
> You would be better off using the =NOW() function, the only problem
> with this is that it will alter every time the sheet is recalculated.
> The code below will call the NOW() function, format the cell
> appropriately and then changes the cell contents from a formula to a
> value.
>
> Sub TimeStamp()
> ActiveCell.FormulaR1C1 = "=NOW()"
> With Selection
> .NumberFormat = "h:mm:ss;@"
> .Formula = .Value
> End With
> End Sub
>
> HTH
>
> DominicB

#8 06-24-2005, 08:05 PM

Better:

Public Sub InsertTime()
With ActiveCell
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
End Sub

In article <jemcgimpsey-41B6C2.17395424062005@msnews.microsoft.com>,
JE McGimpsey <jemcgimpsey@mvps.org> wrote:

> Public Sub InsertTime()
> With ActiveCell
> .Value = Time
> .NumberFormat = "hh:mm:ss"
> End With
> End Sub

Welcome to the Excel Forum

Forum Statistics