Find begining and end of a DATE range to extract

mnsarma

New Member
Joined
Mar 23, 2014
Messages
1
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.
 
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top