If then statement based on time of day

[Sabosis]

Hello-

I am trying to update a cell with text (time of day) based on the time
the report is actually run. The report runs at roughly 15 past the
hour 3 times a day, but I dont want the report to show the actual time
but rather the "top of the hour" time as shown in the code. The code
doesnt work though, any ideas?

Range("A2").Select
If Time < TimeSerial(11, 30, 0) Then
Range("A2").Select
ActiveCell.FormulaR1C1 = "11:00"
ElseIf Time < TimeSerial(2, 30, 0) Then
Range("A2").Select
ActiveCell.FormulaR1C1 = "2:00"
ElseIf Time < TimeSerial(5, 30, 0) Then
Range("A2").Select
ActiveCell.FormulaR1C1 = "5:00"
End If

 

[joeu2004]

I am trying to update a cell with text (time of day) based
on the time the report is actually run. The report runs at
roughly 15 past the hour 3 times a day, but I dont want the
report to show the actual time but rather the "top of the
hour" time as shown in the code. The code doesnt work though,
any ideas?

In what way does it not work? Gives examples. "When the time is
this, I expect that, but I get this instead".

Range("A2").Select
If Time < TimeSerial(11, 30, 0) Then
Range("A2").Select
ActiveCell.FormulaR1C1 = "11:00"
ElseIf Time < TimeSerial(2, 30, 0) Then
Range("A2").Select
ActiveCell.FormulaR1C1 = "2:00"
ElseIf Time < TimeSerial(5, 30, 0) Then
Range("A2").Select
ActiveCell.FormulaR1C1 = "5:00"
End If

I suspect you want:

Sub doit()
Dim t As Date
Select Case Time
Case Is < TimeSerial(11, 30, 0)
t = TimeSerial(11, 0, 0) '11:00 AM
Case Is < TimeSerial(14, 30, 0)
t = TimeSerial(14, 0, 0) '2:00 PM
Case Else
t = TimeSerial(17, 0, 0) '5:00 PM
End Select
Range("A2") = t
Range("A2").NumberFormat = "h:mm am/pm"
End Sub

But I don't understand that logic. I presume that the report
__should_be__ at 11:00-, 2:00- and 5:30-ish. But why not capture the
hour of the day that the report is actually run?

To that end:

Sub doit()
Range("A2") = TimeSerial(Hour(Time), 0, 0)
Range("A2").NumberFormat = "h:mm am/pm"
End Sub

In both cases, add Range("A2").Select only if you want the cursor to
be positioned in A2 when you are done. Normally, that is not the case.

 

[joeu2004]

PS....

Geesh, Google Groups is slow today.

Sub doit()
Dim t As Date
Select Case Time
  Case Is < TimeSerial(11, 30, 0)
     t = TimeSerial(11, 0, 0)   '11:00 AM
  Case Is < TimeSerial(14, 30, 0)
     t = TimeSerial(14, 0, 0)   '2:00 PM
  Case Else
     t = TimeSerial(17, 0, 0)   '5:00 PM
End Select
Range("A2") = t
Range("A2").NumberFormat = "h:mm am/pm"
End Sub

I a.s.s-u-me-d the interesting times were intended to be 11:00 AM,
2:00 PM and 5:00 PM. You never explained. Sigh.

If instead you truly meant 2:00 AM, 5:00 AM and 11:00 AM (!), you
needed to change the order of some of your comparisons. Namely:

Sub doit()
Dim t As Date
Select Case Time
Case Is < TimeSerial(2, 30, 0)
t = TimeSerial(2, 0, 0) '2:00 AM
Case Is < TimeSerial(5, 30, 0)
t = TimeSerial(5, 0, 0) '5:00 AM
Case Else
t = TimeSerial(11, 0, 0) '11:00 PM
End Select
Range("A2") = t
Range("A2").NumberFormat = "h:mm am/pm"
End Sub

Note that Select is order-dependent. It is equivalent to writing If-
Then-ElseIf-Else statements.

 

[Sabosis]

PS....

Geesh, Google Groups is slow today.



I a.s.s-u-me-d the interesting times were intended to be 11:00 AM,
2:00 PM and 5:00 PM.  You never explained.  Sigh.

If instead you truly meant 2:00 AM, 5:00 AM and 11:00 AM (!), you
needed to change the order of some of your comparisons.  Namely:

Sub doit()
Dim t As Date
Select Case Time
   Case Is < TimeSerial(2, 30, 0)
      t = TimeSerial(2, 0, 0)    '2:00 AM
   Case Is < TimeSerial(5, 30, 0)
      t = TimeSerial(5, 0, 0)    '5:00 AM
   Case Else
      t = TimeSerial(11, 0, 0)   '11:00 PM
End Select
Range("A2") = t
Range("A2").NumberFormat = "h:mm am/pm"
End Sub

Note that Select is order-dependent.  It is equivalent to writing If-
Then-ElseIf-Else statements.

Thanks, this worked perfectly, I appreciate the help.

Scott