Use Date Column to Determine Percentage in Another Column.

  • I have this file with three date columns. The first date column is Decision Date, and that column will either have a date or be zero.
    I use the other two date columns to calculate the number of days between them in another column, column D. What I want to do in column E is the following:
    if the Decision Date is 0 and the number in column D is between 0 and 90, then E2 should be 75%; if the number in D is between 91 and 150, E2 should be 50%;
    if the number in D is >150, then E2 should be 25%.


    I want to do the same thing when the Decision Date column has dates in it, with the following changes: if the number in D is between 0 and 210, E2 is 75%; if D is between 211 and 335, E2 is 50%; if D is >336, E2 is 25%.


    I have attached a file with an example. Any help is greatly appreciated.

  • Re: Use Date Column to Determine Percentage in Another Column.


    IN E2 and copy/drag down.....



    =IF(A2=0,IF(D2<=90,75,IF(D2<=150,50,25)),IF(A2>0,IF(D2<=210,75,IF(D2<=335,50,25))))


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Use Date Column to Determine Percentage in Another Column.


    Alternative attached, using lookup tables to make your parameters variable. The resulting formula is:


    =VLOOKUP(D2,IF(A2>0,Table1,Table2),2,TRUE)