Finding a date in between a range of dates.
 

I need help trying to pull information within a start date and end date. The
order date should be between the start and end date and then pull the correct
discount that was applied at that time.
ex...
Record# Start Date End Date Disc
001 4/15/2006 4/15/2007 12.5
002 3/15/2005 4/14/2006 13.5
003 2/15/2004 4/13/2006 14.2

If the order date is 10/28/2005 then it should pull discount 13.5 because the
order date falls within that range.
I need to figure out how to program Excel to figure this out. I have 36,000
lines that I need to check against. Can anybody help me with this?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Finding a date in between a range of dates.
 

Yes it is just sample info. I meant to have the end date of record 003 to be
a day before the start date of record 002. Sorry about that.

sali wrote:
is this exact example of data, or just info?
because, your example of 10/28/2005 has *two* matches, record #003 and
record#002.

are you looking for macro or formula solution?

I need help trying to pull information within a start date and end date. The
order date should be between the start and end date and then pull the correct
[quoted text clipped - 13 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

--
Message posted via http://www.officekb.com

Finding a date in between a range of dates.
 

A formula would probably be easier but I know a little about macros and have
been checking into both.

Abro wrote:
Yes it is just sample info. I meant to have the end date of record 003 to be
a day before the start date of record 002. Sorry about that.

is this exact example of data, or just info?
because, your example of 10/28/2005 has *two* matches, record #003 and
[quoted text clipped - 7 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

--
Message posted via http://www.officekb.com

Finding a date in between a range of dates.
 

is this exact example of data, or just info?
because, your example of 10/28/2005 has *two* matches, record #003 and
record#002.

are you looking for macro or formula solution?


"Abro via OfficeKB.com" <u21539@uwe wrote in message
news:70e683b07a41e@uwe...
I need help trying to pull information within a start date and end date.
The
order date should be between the start and end date and then pull the
correct
discount that was applied at that time.
ex...
Record# Start Date End Date Disc
001 4/15/2006 4/15/2007 12.5
002 3/15/2005 4/14/2006 13.5
003 2/15/2004 4/13/2006 14.2

If the order date is 10/28/2005 then it should pull discount 13.5 because
the
order date falls within that range.
I need to figure out how to program Excel to figure this out. I have
36,000
lines that I need to check against. Can anybody help me with this?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

Finding a date in between a range of dates.
 

"Abro via OfficeKB.com" <u21539@uwe wrote in message
news:70e6b45e5194a@uwe...
A formula would probably be easier but I know a little about macros and
have
been checking into both.

Abro wrote:
Yes it is just sample info. I meant to have the end date of record 003 to
be
a day before the start date of record 002. Sorry about that.

is this exact example of data, or just info?
because, your example of 10/28/2005 has *two* matches, record #003 and
[quoted text clipped - 7 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1

--
Message posted via http://www.officekb.com


formula solution uses "vlookup(value;range;kolumn_result)" which needs data
to be sorted ascending on lookup column. returns row which is equal or
smaller.

so your data:

A B C D
Record# Start Date End Date Disc
001 4/15/2006 4/15/2007 12.5
002 3/15/2005 4/14/2006 13.5
003 2/15/2004 3/14/2005 14.2

needs to be sorted on column B Start Date like:

A B C D
Record# Start Date End Date Disc
003 2/15/2004 3/14/2005 14.2
002 3/15/2005 4/14/2006 13.5
001 4/15/2006 4/15/2007 12.5

to retreive value, put formula
=vlookup(ref_cell,$B$2:$D$4,3)
where ref_cell contains data you are looking for, f.e 10/28/2005, and it
returns 13.5 since Start Date 3/15/2005 is less or equal of searched date
10/28/2005

this solution suppose that all intervals are covered, and there is no date
without Disc value

Finding a date in between a range of dates.
 

Thank you very much! I will try this out.

sali wrote:
A formula would probably be easier but I know a little about macros and have
been checking into both.
[quoted text clipped - 10 lines]
--
Message posted via http://www.officekb.com

formula solution uses "vlookup(value;range;kolumn_result)" which needs data
to be sorted ascending on lookup column. returns row which is equal or
smaller.

so your data:

A B C D
Record# Start Date End Date Disc
001 4/15/2006 4/15/2007 12.5
002 3/15/2005 4/14/2006 13.5
003 2/15/2004 3/14/2005 14.2

needs to be sorted on column B Start Date like:

A B C D
Record# Start Date End Date Disc
003 2/15/2004 3/14/2005 14.2
002 3/15/2005 4/14/2006 13.5
001 4/15/2006 4/15/2007 12.5

to retreive value, put formula
=vlookup(ref_cell,$B$2:$D$4,3)
where ref_cell contains data you are looking for, f.e 10/28/2005, and it
returns 13.5 since Start Date 3/15/2005 is less or equal of searched date
10/28/2005

this solution suppose that all intervals are covered, and there is no date
without Disc value

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1