Conditional Formatting based on time (timer)

[Tom Rosati]

Hi there,
I work in a hospital. I'm creating a sheet to monitor the client's waiting time.
I would like the color of the cell to turn green the moment the arrival time is inserted, to turn yellow after 15min of wait, to turn orange after 30mins and red after 1 hour.
I'm struggling to write conditional formatting that would work for this.
I am assuming the sheet will need to refresh frequently in order to evaluate the time difference btw real-time and arrival time. I am considering the option to run this through Google Sheets, so I would be able to check stuff from the remote. I am assuming conditional formatting rules should be the same, is this correct?

The time of arrival is currently inserted manually but I am also wondering if there is an easy way to allow automatic insertion of arrival time (based on real-time) by simply clicking on the cell. Occasionally, arrival time is inserted at a later stage, so I would still need the option to insert it manually.

Any advice would be highly appreciated!

Thanks

 

[Anthony47]

The following information refers to Excel
For the conditional formats,
-assuming that Z1 contains =NOW()
-if for example the arrival date is in column F
-then you may set a conditional format for yellow using in column F (for example in F1:F100) the criteria "use a formula to determine which cell formatting" and the formula =AND($Fa<>"",(Z1-$F1)>15/1440) 15/1440 means 15 minutes
-you may add additional conditions using 30/1440 (Orage) and 60/1440 (Red)
Of corse this requires that Z1 ("Now") be updated frequently; if your procedures cannot guarantee this then you should consider to schedule a macro every 1 minute using OnTime)

For automatic insertion of a time, I should suggest using the event BeforeDoubleClick: rightclick on the tab with the name of the sheet involved; select Display Code; copy the following code and insert it into the vba module that is now open

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 6 Then       '6=F
    If Target.Value = "" Then
        Target.Value = Now()
    Else
        Beep
    End If
End If
Cancel = True
End Sub

Return to Excel and try doubleclicking in a cell in column F: if its empty, the timing will be inserted; if it's not empty the cell will not change (if you wish modify the timing already insert, either do it manually or first clear the cell then doubleclick)

Try...

 

[Anthony47]

In the above message the first formula should be:

=AND($F1<>"",(Z1-$F1)>15/1440)

(I don't know how it became $Fa<> etc etc)

 

[Tom Rosati]

Thanks for your help.
The double-click function works perfectly!

I also managed to run a Module on VBA to refresh the page automatically every 5s:
Sub Calculate_Range()
Range("C1:C14").Calculate
Application.OnTime DateAdd("s", 5, Now), "Calculate_Range"
End Sub

Unfortunately I have not been able to reproduce your conditional formatting as the tab will always remain white.
Do I need to apply a specific cell formats to the interested columns?

Thanks again for your time

 

[Anthony47]

I am sorry, in the C.F. formula Z1 need to be an absolute address:

=AND($F1<>"",($Z$1-$F1)>15/1440)

Select F1:Fxx (the range you expect to fill with date & time), start the CF, chose New Rule /Use a formula; type in the formula; select the desired format (yellow for 15/1440); complete the CF with the two additional rules

Results should be as in the picture

And, by the way, this was tested on Windows...