Excel worksheet programming for time restrictions.

B

bhaynes

I have several cells (properties) that will be visible or
not depending on the time of day. I am using VBA to set
the value of the properties and need a way to use not just
the TIME function to establish the system time but, a way
to test for a period of time, for example the DAY SHIFT,
SWING SHIFT AND GRAVEYARD SHIFT. Using If Then Else
conditional statements to test the system time to a span
of time to indicate the shifts aforesaid.
What I am not clear on is the syntax in VBA to use when
identifying the shifts and testing for conditions met.
HElP?
 
B

Bob Phillips

Is this any help?

Dim tim0800
Dim tim1500
Dim tim2200
Dim tim2400
Dim tim0000
Dim tim0500

tim0800 = TimeSerial(8, 0, 0)
tim1500 = TimeSerial(15, 0, 0)
tim2200 = TimeSerial(22, 0, 0)
tim2400 = TimeSerial(24, 0, 0)
tim0000 = TimeSerial(0, 0, 0)
tim0500 = TimeSerial(5, 0, 0)

If (Range("A1").Value >= tim0800 And Range("A1") < tim1500) Then
MsgBox "Day Shift"
ElseIf (Range("A1").Value >= tim1500 And Range("A1") < tim2200) Then
MsgBox "Swing Shift"
ElseIf (Range("A1").Value >= tim2200 And Range("A1") <= tim2400) Then
MsgBox "Graveyard Shift"
ElseIf (Range("A1").Value >= tim0000 And Range("A1") < tim0500) Then
MsgBox "Graveyard Shift"
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top