I have a formula that works fine in excel 2007, but contains too many levels of nesting to work in 2003. I don’t know if there is a solution or not, but if there is the people here will know it. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o></o>
My spreadsheet has 6 inputs: <o></o>
<o></o>
1) Time period (i.e., Daily, Weekly, or Yearly) – Cell C5<o></o>
2) Beginning Date – Cell D8 (the beginning date shows up in cell L1 and goes to V1 in increments based on the Time period selected)<o></o>
3) Today’s date – Cell D9<o></o>
4) Due Date – Cell G16<o></o>
5) Revised Due Date – Cell H16<o></o>
6) Date Completed – Cell I16<o></o>
<o></o>
Based on these inputs I want either a “” or a “1” or a “0” to show up in L16 – V16. Here are the possibilities:<o></o>
<o></o>
· 4, 5, 6 blank so I should get a “” in L16 – V16<o></o>
· Due Date < Today’s Date – results in a 0 in the cell that corresponds to the due date, all others “”<o></o>
· Due Date < Today’s Date and Revised Due Date > Today’s Date – results in a 0 in the cell corresponding with the original due date and a “” in all others<o></o>
· Due Date < Today’s Date and Revised Due Date > Today’s Date – Both receive 0 all others “”<o></o>
· Date Completed < Due Date – results in a 1 in the cell corresponding with the original due date<o></o>
· Date Completed > Due Date and Date Completed < Revised Due Date and Date Completed < Today’s Date – results in a 0 for the Due date and a 1 for the Revised Due Date<o></o>
<o></o>
Here is my formula that works (from Cell L16 - this goes over to V16):<o></o>
<o></o>
=IFERROR(IF($G16>$D$9,"",IF(AND($D$9>$G$16,OR($I16=0,$I16>$G16),IFERROR(MATCH($G16,$L$1:$AJ$1)=COLUMN()-11,"False")),0,IF(AND($H16>$D$9,$H16>0,$I16=0),"",IF(AND($I16>1,OR($I16<=$G16,$C$5<>"Daily"),$H16="",IFERROR(MATCH($G16,$L$1:$AJ$1)=COLUMN()-11,"False")),1,IF(IFERROR(MATCH($G16,$L$1:$AJ$1)=COLUMN()-11,"False"),0,IF(AND($H16>=$I16,$I16>0,MATCH($H16,$L$1:$AJ$1)=COLUMN()-11),1,IF(MATCH($H16,$L$1:$AJ$1)=COLUMN()-11,0,""))))))),"")
Thank you for any help!<o></o>
fficeffice" /><o></o><o
></o>My spreadsheet has 6 inputs: <o
></o><o
></o>1) Time period (i.e., Daily, Weekly, or Yearly) – Cell C5<o
></o>2) Beginning Date – Cell D8 (the beginning date shows up in cell L1 and goes to V1 in increments based on the Time period selected)<o
></o>3) Today’s date – Cell D9<o
></o>4) Due Date – Cell G16<o
></o>5) Revised Due Date – Cell H16<o
></o>6) Date Completed – Cell I16<o
></o><o
></o>Based on these inputs I want either a “” or a “1” or a “0” to show up in L16 – V16. Here are the possibilities:<o
></o><o
></o>· 4, 5, 6 blank so I should get a “” in L16 – V16<o
></o>· Due Date < Today’s Date – results in a 0 in the cell that corresponds to the due date, all others “”<o
></o>· Due Date < Today’s Date and Revised Due Date > Today’s Date – results in a 0 in the cell corresponding with the original due date and a “” in all others<o
></o>· Due Date < Today’s Date and Revised Due Date > Today’s Date – Both receive 0 all others “”<o
></o>· Date Completed < Due Date – results in a 1 in the cell corresponding with the original due date<o
></o>· Date Completed > Due Date and Date Completed < Revised Due Date and Date Completed < Today’s Date – results in a 0 for the Due date and a 1 for the Revised Due Date<o
></o><o
></o>Here is my formula that works (from Cell L16 - this goes over to V16):<o
></o><o
></o>=IFERROR(IF($G16>$D$9,"",IF(AND($D$9>$G$16,OR($I16=0,$I16>$G16),IFERROR(MATCH($G16,$L$1:$AJ$1)=COLUMN()-11,"False")),0,IF(AND($H16>$D$9,$H16>0,$I16=0),"",IF(AND($I16>1,OR($I16<=$G16,$C$5<>"Daily"),$H16="",IFERROR(MATCH($G16,$L$1:$AJ$1)=COLUMN()-11,"False")),1,IF(IFERROR(MATCH($G16,$L$1:$AJ$1)=COLUMN()-11,"False"),0,IF(AND($H16>=$I16,$I16>0,MATCH($H16,$L$1:$AJ$1)=COLUMN()-11),1,IF(MATCH($H16,$L$1:$AJ$1)=COLUMN()-11,0,""))))))),"")
Thank you for any help!<o
></o>
