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

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, Procedu="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" wrote:

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

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

Thanks again

"Mike H" wrote:

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, Procedu="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" wrote:

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

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


Mike

"albertmb" wrote:

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

Thanks again

"Mike H" wrote:

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, Procedu="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" wrote:

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

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

Thanks again

"Mike H" wrote:

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, Procedu="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" wrote:

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

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

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

Thanks again

"Mike H" wrote:

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, Procedu="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" wrote:

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

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

Albert

"Mike H" wrote:

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

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

Thanks again

"Mike H" wrote:

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, Procedu="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" wrote:

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

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"albertmb" wrote in message
...
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

=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.
--
David Biddulph

Bernard Liengme wrote:
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
best wishes

"albertmb" wrote in message
...
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

Glad to help and thanks for the feedback

"albertmb" wrote:

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

Albert

"Mike H" wrote:

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

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

Thanks again

"Mike H" wrote:

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, Procedu="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" wrote:

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

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(E 6,$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

"Mike H" wrote:

Glad to help and thanks for the feedback

"albertmb" wrote:

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

Albert

"Mike H" wrote:

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

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

Thanks again

"Mike H" wrote:

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, Procedu="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" wrote:

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

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

"Bernard Liengme" wrote:

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"albertmb" wrote in message
...
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