Excel worksheet programming for time restrictions.

  • Thread starter Thread starter bhaynes
  • Start date Start date
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?
 
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)
 
Back
Top