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 , "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 |
All times are GMT +1. The time now is 06:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com