Comparing dates, but some date fields are blank.

[dgarris]

I have the following table with a header row and date entries. Students fill in a date when they are done with an activity and get some credit for completing each activity.


Activity1 Activity2 Activity3 Activity4
1/1/07 2/1/07 3/1/07 4/1/07
1/5/07 3/4/07


I want to compare the dates against todays date and determine a percent complete value for another cell in the worksheet. Here is the formula in that cell.

cell a5 contains: if(today>d1,100%,if(today>c1,75%,if(today>b1,50%,if(today>a1,25%,0%)
result is 50% if todays date is 2/25/07

cell a6 contains: if(today>d2,100%,if(today>c2,75%,if(today>b2,50%,if(today>a2,25%,0%)
resulut is 100% if todays date is 2/25/07 (it really should be 25% because the student only completed activity1 so far.


The problem is that this formula works fine for line 1 but not for line2 because of the blanks in some of the date fields. How can I solve this without doing further nesting of Ifs? My real problem is similar but I am nearly at my conditional nesting limit of 7 deep.

[Paul]

dgarris, how about this formula:

Please Login or Register  to view this content.

See my example sheet. I put the formula in cell E2 and copied it down.