CODEWITHSUNDEEP
excelvba
Andrei Ion
Andrei Ion

Reputation: 1827

Time operation in VBA

Here's what I want to do and I realized it wasn't working.

If Time > 23 And Time < 7 Then
   ws.Cells(Target.Row, 12).Value = 3
ElseIf Time > 7 And Time < 15 Then
   ws.Cells(Target.Row, 12).Value = 1
Else
   ws.Cells(Target.Row, 12).Value = 2
End If

What I want to do with this stuff... if the actual time is over 11 PM but less than 7 am... it writes 3 in a cell... and so on... The problem is that I realized that this comporison Time > 23 or Time < 7 doesn't work... how can I compare Time function with the actual hour? Thanks!

Upvotes: 0

Views: 8552

Answers (3)

Steve Robillard
Steve Robillard

Reputation: 13471

Give this a try

If Hour(now) > 23 or Hour(now) <= 7 Then
   ws.Cells(Target.Row, 12).Value = 3
ElseIf Hour(now) > 7 And Hour(now) < 15 Then
   ws.Cells(Target.Row, 12).Value = 1
Else
   ws.Cells(Target.Row, 12).Value = 2
End If

Upvotes: 3

brettdj
brettdj

Reputation: 55682

Something like this. I have used sample variables in place of Target and knowing which sheet ws was

Sub Timing()
    Dim ws As Worksheet
    Dim dbTime As Double
    Set ws = Sheets(1)
    dbTime = Time
    If dbTime > 23 / 24 Or dbTime < 7 / 24 Then
        ws.Cells(1, 12).Value = 3
    ElseIf dbTime >= 7 / 24 And dbTime < 15 / 24 Then
        ws.Cells(1, 12).Value = 1
    Else
        ws.Cells(1, 12).Value = 2
        End If
End Sub

Upvotes: 3

PhilPursglove
PhilPursglove

Reputation: 12589

You need to extract the hour from the time, which you can do with the DatePart function e.g.

Dim CurrentHour
CurrentHour = DatePart("h", Time)

If CurrentHour > 23 Or CurrentHour < 7 Then
....

Upvotes: 1

Related Questions