Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find That Date
Hi there,
Can you please help me to slove this problem. I have two spreadsheets the first with a list of dates and other info. And the second with a table like the one below. Year/Period Month Start Period End Period 2004-01 April-2004 01-Apr-04 23-Apr-04 2004-02 May-2004 24-Apr-04 21-May-04 2004-03 June-2004 22-May-04 25-Jun-04 2004-04 July-2004 26-Jun-04 23-Jul-04 I want to look up the date in the first spreadseet to see if it falls between the start and end period and bring me back the year/period. Notice that the periods may have two months in it. I cannot use a nested if statement because I have a number of years to consider. I could use a maco to test each line but wanted to know if there is a formular that I can use insead. Thanks in advance Denz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find That Date
Assuming that the periods are sorted and contiguous, I would use
=INDEX(period_range,MATCH(date,start_range)) Since you posted to the programming newsgroup, you may want to do this from VBA; use Application.INDEX, Application.MATCH, and range objects. Jerry Denz wrote: Hi there, Can you please help me to slove this problem. I have two spreadsheets the first with a list of dates and other info. And the second with a table like the one below. Year/Period Month Start Period End Period 2004-01 April-2004 01-Apr-04 23-Apr-04 2004-02 May-2004 24-Apr-04 21-May-04 2004-03 June-2004 22-May-04 25-Jun-04 2004-04 July-2004 26-Jun-04 23-Jul-04 I want to look up the date in the first spreadseet to see if it falls between the start and end period and bring me back the year/period. Notice that the periods may have two months in it. I cannot use a nested if statement because I have a number of years to consider. I could use a maco to test each line but wanted to know if there is a formular that I can use insead. Thanks in advance Denz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Specific date in Biwwekly Based on date | Excel Discussion (Misc queries) | |||
Julian date - find next highest date/number | Excel Worksheet Functions | |||
Find the date of the coming up Saturday given the current date. | Excel Worksheet Functions | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions |