Find begining and end of a DATE range to extract

[mnsarma]

Hi Everyone,

I have been breaking my head around the way of extracting a date range in my excel sheet. Here are the requirements:-

1) My sheet contains 2 Columns ("Date" & "Product ID") and below is a sample set of records and their formats (sometimes I have multiple records with same date:-

Date ProductID
---------------------------
18/01/2014 XXXX
21/01/2014 YYYY
22/01/2014 ZZZZ

2) This data comes as an input to my sheet from a source that I cannot change. The "Date" comes as Text/General format but not as Date because of which I cannot simply use a comparision and also not in "mm/dd/yyyy".

3) I have to take two dates from users (don't worry about that format as I can modify it into anything I want) and extract the product ID from above sheet.

I have used simple "Find" functionality and could extract information but it works only when I enter valid dates that are available in my excel sheet. It always fails in below cases:-

1) start search date = "14/01/2014" and end search date = "24/01/2014"
2) start search date = "15/01/2014" and end search date = "21/01/2014"
3) start search date = "20/01/2014" and end search date = "23/01/2014"

(Hope these examples make sense. What I am trying to say is some days I might have data and some days I might not but when user enters date, it should atleast give the data that falls in that date range).

The Code that I used for finding the first matching row and last matching row to extract is (please don't try to develop this code but if you have completely new code also that works) :-

#
On Error Resume Next

start_row = Columns("A:A").Find(what:=UserStartingInputDate, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False).row

final_row = Columns("A:A").Find(what:=UserEndInputDate, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= False, SearchFormat:=False).row
#


Any help in this regard would be greatly appreciated. Thanks in advance!!

- Sarma M.

 

[Jubjab]

I did not fully understand your question, but a quick Data-> Text to columns on the dates with option "Date" in Step 3 of 3 should fix the problem with the dates being text.