Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List based on a date
I am looking for a function or code to extract from a list , names of those
persons whose anuual fee payment due date is within next 15 days.This output list be a report or in a separate sheet which always reflect current status of the above function. Example :sheet 1 r/no -- col a------------col b----------------------------col c 1 ---name----------membership date--------------annual fee 2. ----david----------09-feb-2003--------------------$60 3. ------mark----------08-jan-2001--------------------$50 4. ------rosy-----------*********--------------------*** 5. ------cary-----------05-jan-2004---------------------$45 6. ------james---------11-nov-2005---------------------$80 (note: all in the list are not necessarily members) What I need is:(as on today) r/no-----col a--------------col b 1.-------name-------------Due date 2.-------cary--------------05-jan-2006 3.-------mark-------------08-jan-2006 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List based on a date
One play, using non-array formulas ..
Sample construct at: http://www.savefile.com/files/3653714 ListBasedOnDueDates_Tungana_misc.xls Assume table below is in Sheet1, cols A to C, data from row2 down (dates in col B) name membership date annual fee david 09-Feb-03 $60 mark 08-Jan-01 $50 rosy ********* *** cary 05-Jan-04 $45 james 11-Nov-05 $80 Peter 31-Dec-05 $50 James 05-Jan-04 $80 etc (Possibility of duplicate membership dates is also assumed. This is likely.) In a new Sheet2, Enter labels in A1:B1 : Name, Due Date Put in A2: =IF(ISERROR(SMALL($F:$F,ROW(A1))),"", INDEX(Sheet1!A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F, 0))) B2: =IF(ISERROR(SMALL($F:$F,ROW(A1))),"", INDEX(D:D,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0))) D2: =IF(ISNUMBER(Sheet1!B2),IF(TODAY()DATE(YEAR(TODAY ()),MONTH(Sheet1!B2),DAY(S heet1!B2)),DATE(YEAR(TODAY())+1,MONTH(Sheet1!B2),D AY(Sheet1!B2)),DATE(YEAR(T ODAY()),MONTH(Sheet1!B2),DAY(Sheet1!B2))),"") E2: =IF(D2="","",D2-TODAY()) F2: =IF(OR(E2="",E215),"",E2+ROW()/10^10) (Leave F1 empty) (Cols D to F are helper cols) Select A1:F1, copy down to say, F50, to cover the max expected extent of data in Sheet1 Format col B (& col D if desired) as dates Cols A & B will return the required results Names with duplicate due dates, if any, will appear in the same relative order as in Sheet1 For the sample data, we'd get: Name Due Date Peter 31-Dec-05 cary 05-Jan-06 James 05-Jan-06 mark 08-Jan-06 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "TUNGANA KURMA RAJU" wrote in message ... I am looking for a function or code to extract from a list , names of those persons whose anuual fee payment due date is within next 15 days.This output list be a report or in a separate sheet which always reflect current status of the above function. Example :sheet 1 r/no -- col a------------col b----------------------------col c 1 ---name----------membership date--------------annual fee 2. ----david----------09-feb-2003--------------------$60 3. ------mark----------08-jan-2001--------------------$50 4. ------rosy-----------*********--------------------*** 5. ------cary-----------05-jan-2004---------------------$45 6. ------james---------11-nov-2005---------------------$80 (note: all in the list are not necessarily members) What I need is:(as on today) r/no-----col a--------------col b 1.-------name-------------Due date 2.-------cary--------------05-jan-2006 3.-------mark-------------08-jan-2006 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating Based on Date | Excel Discussion (Misc queries) | |||
cell color change based on due date | New Users to Excel | |||
Last Date in List | Excel Discussion (Misc queries) | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions | |||
Show a date based on today | Excel Worksheet Functions |