Current Time

[albertmb]

Hi Everyone,
I need a function that gives me the current time and updates the time in
real time.

I used the NOW() function but it gives me also the date and when I customed
the cell removing the date, it only removes it visualy, and although it
updates it does not update in real time.

I thank you in anticipation.

Albert

 

[Mike H]

Hi,

You can do that but need to be aware that while your PC is updating this
time it's doing nothing else so there is an overhead in doing what you want.
Not a massive one but nevertheless it's there.

Put this code in a general module. Alt + f11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste this code in

Dim UpDate As Date
Sub Recalc()
Sheets("Sheet1").Range("A1").Value = Format(Time, "hh:mm:ss AM/PM")
Call UpDateTime
End Sub

Sub UpDateTime()
UpDate = Now + TimeValue("00:00:01")
Application.OnTime UpDate, "Recalc"
End Sub

Sub StopClock()
On Error Resume Next
Application.OnTime EarliestTime:=UpDate, Procedure:="Recalc", Schedule:=False
End Sub


Then double click 'ThisWorkbook' and paste this in on the right

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopClock
End Sub

Run the sub called 'ReCalc and you get a realtime clock in A1 Sheet 1

Mike

 

[albertmb]

Thanks Mike it worked perfectly, but yet another problem, I inserted a time
in cell B1 and in cell C1 I wrote this formulaB1-A1)*24, the result came
out ok but still with the date included is it possible to fix this?

Thanks again

 

[Mike H]

Format as general and you get lots of decimal places or as number and specify
how many decimal places you want


Mike

 

[albertmb]

I apologise for bothering you again, is it possible to Auto_run this macro
when workbook is opened?

Thanks again

 

[Mike H]

Hi,

Yes it is and it's easy. Double click 'ThisWorkbook' in VB editor and paste
this small module in and it automatically starts the clock on workbook open

Private Sub Workbook_Open()
Recalc
End Sub

Mike

 

[albertmb]

Great Mike, it all worked to perfection. Thank for your time and dedication.

Albert

 

[Bernard Liengme]

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
best wishes

 

[David Biddulph]

=MOD(NOW(),1) would similarly get rid of the whole days, but neither that
nor Bernard's formula will update in real time. It will update only when
the worksheet recalculates.

 

[Mike H]

Glad to help and thanks for the feedback

Great Mike, it all worked to perfection. Thank for your time and dedication.

Albert

 

[albertmb]

Hi Mike, its me again, wonder if you can help me again.
In cell A1 I have =Today()
in cell B1 I have the Time (Your Macro)
From cells E6:E369 I have a calendar
From Cells H6:H369 I wrote this formula,
IF(ISNA(VLOOKUP(E6,$A$1:$B$1,2,FALSE)),0,VLOOKUP(E6,$A$1:$B$1,2,FALSE))
This gives me a '0' where the date does not match and the time where the
date matches.
Is there a way of how I can keep the time in the matching cell without
changing (i.e. stoping the clock in the relevant cell) and keep the result
even when the date changes the next day.

Thank you for all you do with us who want alot and know little.

Regards
Albert

 

[albertmb]

Hi Bernard, thanks for your concern but as David said this formula does not
update time in real time. Still I appreciate.