Time Functions

IS there a function that I can have running in a cell that acts like a clock
- ticks every second ?

If so, can it be fomated so that an ajacent cell can add 30 seconds to the
clock time and round up to the next full minute. For example.

Time Time + 30 Seconds

9:32:22 AM 9:33:00 AM
9:32:45 AM 9:45:00 AM

Thank you in advance

Carl,

You can use the application.ontime method to have the times update at set
time intervals.

http://ForPicks.com

"carl" wrote:

> IS there a function that I can have running in a cell that acts like a clock
> - ticks every second ?
>
> If so, can it be fomated so that an ajacent cell can add 30 seconds to the
> clock time and round up to the next full minute. For example.
>
> Time Time + 30 Seconds
>
> 9:32:22 AM 9:33:00 AM
> 9:32:45 AM 9:45:00 AM
>
> Thank you in advance

You could do it with something like this:

Public Sub UpdateClock(Optional ByVal bStop = False)
Const dONESECOND As Double = 1.15740740740741e-05
Const dTHIRTYSECONDS As Double = 0.000347222222222222
Static rRange As Range
Static dNextTime As Double
Dim dNow As Double
If bStop Then
Application.OnTime dNextTime, "UpdateClock", schedule:=False
Else
If dNextTime = 0 Then
With Sheets("Sheet1").Range("A1:B1")
Set rRange = .Cells
.NumberFormat = "hh:mm:ss AM/PM"
End With
End If
dNow = Now
With rRange
.Cells(1).Value = dNow
.Cells(2).Value = dNow + dTHIRTYSECONDS
End With
dNextTime = dNow + dONESECOND
Application.OnTime dNextTime, "UpdateClock"
End If
End Sub

Public Sub StartClock()
UpdateClock bStop:=False
End Sub

Public Sub StopClock()
UpdateClock bStop:=True
End Sub

You can call StartClock from the Workbook_Open() event macro if you want.

Note that you'll likely take a pretty significant performance hit for
updating so frequently...


In article <[email protected]>,
"carl" <[email protected]> wrote:

> IS there a function that I can have running in a cell that acts like a clock
> - ticks every second ?
>
> If so, can it be fomated so that an ajacent cell can add 30 seconds to the
> clock time and round up to the next full minute. For example.
>
> Time Time + 30 Seconds
>
> 9:32:22 AM 9:33:00 AM
> 9:32:45 AM 9:45:00 AM
>
> Thank you in advance