Finding Start Date

[Guest]

I have a macro in which a user form (frmStartDate) prompts the user for a
start date (which is saved to the variable StartDate). Upon choosing a date
from the calendar, the macro needs to find this date in the spreadsheet. Note
that the dates are given in date/time format (e.g., 7/26/2007 22:00). Any
guidance would be much appreciated.

 

[Guest]

If you want a match, you will need to get rid of the time portion. Something
like
If Format(StartDate, "m/d/yyyy") = Format(Now, "m/d/yyyy") Then
'do something
End If

But you won't be able to get match with the time included.

 

[Rick Rothstein \(MVP - VB\)]

If you want a match, you will need to get rid of the time portion.

Something like
If Format(StartDate, "m/d/yyyy") = Format(Now, "m/d/yyyy") Then
'do something
End If

If StartDate is declared as a Date variable, then you should be able to do
the above test like this...

Dim StartDate As Date
StartDate = #September 6, 2007 9:19PM#
If Int(StartDate) = Date Then
'do something
End If

Rick

 

[Guest]

I always have problems with dates. No matter how many times I work with
them, it seems like there is always some quirk in the code that I want to use
that forces me to fall back and regroup. In most cases I end up converting
everything to text.
Someday, I hope to overcome that.

 

[Guest]

The "Int" did the trick Rick! Here is my code for retaining only the data
between a start and end date.

With Worksheets("WorksheetName")
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Int(Range("A" & i)) <> StartDate Then
.Rows(i).Delete
i = i - 1
Else
Exit For
End If
Next i

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To 30
For j = 2 To lastrow
If Int(Range("A" & j)) = EndDate Then
.Rows(j + 3).Delete
End If
Next j
Next i
End With

 

[Rick Rothstein \(MVP - VB\)]

The "Int" did the trick Rick!

The reason for that is VBA holds dates in a Double data type with the
integer part holding the number of days past "date zero" (which, for VBA is
December 30, 1899) and the fractional part holding the time value as the
fraction of a 24 hour day. The Int function removes the fractional part
which means it removes the time value. That allows you to compare it to a
pure date value.

Rick