user3032425
user3032425

Reputation: 53

DateAdd -1 day in excel vba

Could anyone please help me here. I am trying to print the date as -1 day in case the time falls between 12:00 Am to 04:00 AM. If it is from 04:00 Am to 11:59:59 Pm should show current date.

I am struggling a lot but no desired result.

If cTime < "04:00:00 AM" Or cTime >= "12:00:00 AM" Then    
    Workbooks("Check").Sheets(1).Cells(lastrow + 1, 1).Value = DateAdd("d", -1, Date)    
Else    
    Workbooks("Check").Sheets(1).Cells(lastrow + 1, 1).Value = Date
End If

Upvotes: 0

Views: 782

Answers (1)

Busse
Busse

Reputation: 863

I am unaware of what cTime is, but in the past when I have worked with time operations like this [since they are simple times], why not try the Hour function?

If Hour(Now) >= 0 And Hour(Now) <= 4 Then
    Workbooks("Check").Sheets(1).Cells(lastrow + 1, 1).Value = DateAdd("d", -1, Date)
Else    
    Workbooks("Check").Sheets(1).Cells(lastrow + 1, 1).Value = Date
End If

For your original code, you also should make sure you are using AND, and not OR. If the current time is 11:00AM (which it is roughly for me), then that meets the criteria Hour(Now) > 0 (12AM). But it doesn't meet the criteria Hour(Now) < 4 (4AM). Since it's an OR, it only needs to meet A, not A and B.

Upvotes: 1

Related Questions