Simple time difference

[itsupport]

Hi

Maybe a simple request but i just need direction.

I have a sheet that users enter a reference number and the time that they contacted a user.
I also have a "now" field with the current time.
Our users need to make contact with theses other user on the hour so need an alarm to remind them when to do this.
I basically need the "now" cell to look at the recorded time and it its over an hour to pop a single word in a cell next to it stating "Escalate"

 

[Akashwani]

Hi,

This will only work IF the worksheet is being updated. =NOW() will not change unless the worksheet has changed.

Excel Workbook
	A	B	C	D	E
1	Reference	Contact Time	Status	Now	Time Period
2	AK1234	16:50:00	ESCALATE	18:35:39	01:00:00
3	Ak1235	17:45:00	*	*	*
4	AK1236	18:00:00	*	*	*
5	AK1237	17:34:00	ESCALATE	*	*
Sheet1

You would need some VBA code to continuously update the time in D2.
I don't write or understand VBA, so I cannot help you with the following code, sorry. I have taken it from a workbook I have as it is.

Press alt F11, click on This Workbook and paste the following code into the white area..

Dim bolOpening As Boolean
Dim t As Date
Private Sub Workbook_Open()

Flag = True
    RunWhen = Now + TimeSerial(0, 0, 1)
    Application.OnTime RunWhen, "UpdateClock"

t = Now

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    Flag = False
    Call StopClock

End Sub

This needs to be put into a Module, whilst still in the VBA window, click on Insert click Module and paste this code into the white area...

Public Flag As Boolean
Public RunWhen As Double
Sub UpdateClock()
    If Flag = True Then
'       *** Change Sheet name and Range reference to suit ***
        Worksheets("Sheet1").Range("D2").Calculate
        RunWhen = RunWhen + TimeSerial(0, 0, 1)
        Application.OnTime RunWhen, "UpdateClock"
    End If
End Sub


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

I hope that you find this helpful, good luck.

Ak

 

[itsupport]

Thank you very much...not had time to test yet but looks ideal!!!

i will let you know