Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Start Date
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Start Date
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. "GDCross" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Start Date
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Start Date
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. "Rick Rothstein (MVP - VB)" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Start Date
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)" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Start Date
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard for finding items that start with ? | Excel Discussion (Misc queries) | |||
Calculating monthly start date with a start date | Excel Worksheet Functions | |||
Determining an annual review date from an employee start date | Excel Worksheet Functions | |||
Finding concurrent events from a list with a start and an end time | Excel Discussion (Misc queries) | |||
VBA that automatically generates a series of cash flows based on a start date, an end date and frequency | Excel Programming |