Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding dates in a range | Excel Discussion (Misc queries) | |||
finding if a date falls between two dates | Excel Discussion (Misc queries) | |||
finding a range of dates to total | Excel Discussion (Misc queries) | |||
Finding dates within a date range | Excel Worksheet Functions | |||
Finding Dates in a date range | Excel Discussion (Misc queries) |