Evaluating Dates in VBA

[diamantebonita]

Arrrrgh!! how come dates are so hard to work with? I really thought this was gonna be a quick and easy program to write.

I want to check the date of the items in the list and determine if the date is today and/or within 7 days from today. This is how I determine the list that will be displayed. But i'm not able to get any of the DATE functions or statements to work.

Private Sub Workbook_Open()
Dim msg As String
Dim vendorL As String
Dim amountL As String
Dim duedateL As String



Sheets("Bills").Activate
Range("G2").Activate

If ActiveCell.Value = WorksheetFunction.Date() And ActiveCell.Value < WorksheetFunction.Date() + 7 Then
vendorL = ActiveCell.Offset(0, 2)
amountL = ActiveCell.Offset(0, 1)
duedateL = ActiveCell.Value
End If

Dim TodayDate As String



TodayDate = Format(Date, "Long Date")

msg = "Hello Genese!" & vbCrLf & vbCrLf
msg = msg & "Today is " & TodayDate & "." & vbCrLf
msg = msg & "These are the bills that need to be paid this week." & vbCrLf & vbCrLf
msg = msg & vendorL & vbTab & amountL & vbTab & duedateL & vbCrLf
MsgBox msg, vbInformation + vbOKOnly, "Bills To Pay"



End Sub

 

[Jonmo1]

The logic is almost there...

If ActiveCell.Value = WorksheetFunction.Date() And ActiveCell.Value < WorksheetFunction.Date() + 7 Then

Read that, and think about it.....

If activecell.value is say Today+3, then it certainly does not = Today, therefor the AND is false.


Also, nothing wrong with the way you did it..but you don't need to use the worksheetfunction.Date...
VBA has it's own built in Date function...called..... Date.


Try

If ActiveCell.Value >= Date And ActiveCell.Value < Date + 7 Then


Hope that helps...

 

[Scott R]

If ActiveCell.Value = WorksheetFunction.Date() And ActiveCell.Value < WorksheetFunction.Date() + 7 Then

Use Date instead of WorksheetFunction.Date(). I think you want,

If ActiveCell.Value <= Date +7 Then

 

[diamantebonita]

wow! that helped tremendously! but I feel as if I did that as well and it just didnt work before.
Thanks alot!

 

[diamantebonita]

Thanks!!!