I am having difficulty in trying accomplish the following in a formula:
"where 1 or more value is equal to itself in a range - take its corresponding difference once and add to the dependent task date"
i have 4 columns of data:
1. Estimated Task Due Date (column a) (auto-calc formula)
2. Enter Revised Action Date (column b) (user enters a manual date if date given in column a does not work for them)
3. Dependency Re-Calc Date (column c) (need formula for this)
4. # of Days Difference b/n "Estimated Task Due Date" and "Enter Revised Action Date" (i.e. A1-B1 - A1's date will always be further out than B1 due to its formula)
I designed this simple formula to compare 2 cells to each other and need to accomplish the same thing with regard to a range:
=IF(A1=B2, D1+A3, IF(A1<>B24, SUM(D1+D2)+A3))
The above formula (highlighted in yellow below) is comparing 3 tasks -- Task/Line 3 is dependent on Tasks/Lines 1 & 2. Therefore, if manual dates are entered in column b -- the difference b/n the estimated date given in column a and the new manual date in column b needs to be added to Task/Line 3 Estimated Task Due Date.
<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=421 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 39pt" height=52><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 39pt; BACKGROUND-COLOR: gray" width=115 height=52>Estimated Task Due Date</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: gray" width=107>Enter Revised Action Date</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: gray" width=105>Dependency Re-Calc Date</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: gray" width=94># of Days Difference</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=421 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=115 height=34 x:num="39668">8/8/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107 x:num="39666">8/6/2008</TD><TD class=xl27 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #969696; WIDTH: 79pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=105></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94 x:num>2</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=115 height=51 x:num="39671">8/11/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107 x:num="39667">8/7/2008</TD><TD class=xl27 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696; WIDTH: 79pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=105></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94 x:num>4</TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 24.95pt; BACKGROUND-COLOR: transparent" width=115 height=33 x:num="39665">8/5/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107></TD><TD class=xl27 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696; WIDTH: 79pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=105></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 24.95pt; BACKGROUND-COLOR: transparent" width=115 height=33 x:num="39666">8/6/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107></TD><TD class=xl34 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696; WIDTH: 79pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=105></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 24.95pt; BACKGROUND-COLOR: transparent" width=115 height=33 x:num="39682">8/22/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107 x:num="39675">8/15/2008</TD><TD class=xl35 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" width=105 x:num="39688">8/28/2008</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94 x:num>7</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=115 height=40 x:num="39668">8/8/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=105 x:num="39674">8/14/2008</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94></TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=115 height=40 x:num="39682">8/22/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107 x:num="39675">8/15/2008</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=105 x:num="39688">8/28/2008</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94 x:num>7</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=115 height=40 x:num="39668">8/8/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=105 x:num="39674">8/14/2008</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94></TD></TR></TBODY></TABLE>
The problem I am running into is when it comes time to compare more than 2 cells of dates. If you look to the left of the cell highlighted in yellow you will see a date of 8/15. Two lines below the 8/15 shows again. I need the formula to reflect when the dates equal to take the difference of 7 only once. Hopefully this is making sense...
Any help any one can provide is greatly appreciated! I've spent hours on this already trying to figure it out.
"where 1 or more value is equal to itself in a range - take its corresponding difference once and add to the dependent task date"
i have 4 columns of data:
1. Estimated Task Due Date (column a) (auto-calc formula)
2. Enter Revised Action Date (column b) (user enters a manual date if date given in column a does not work for them)
3. Dependency Re-Calc Date (column c) (need formula for this)
4. # of Days Difference b/n "Estimated Task Due Date" and "Enter Revised Action Date" (i.e. A1-B1 - A1's date will always be further out than B1 due to its formula)
I designed this simple formula to compare 2 cells to each other and need to accomplish the same thing with regard to a range:
=IF(A1=B2, D1+A3, IF(A1<>B24, SUM(D1+D2)+A3))
The above formula (highlighted in yellow below) is comparing 3 tasks -- Task/Line 3 is dependent on Tasks/Lines 1 & 2. Therefore, if manual dates are entered in column b -- the difference b/n the estimated date given in column a and the new manual date in column b needs to be added to Task/Line 3 Estimated Task Due Date.
<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=421 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 39pt" height=52><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 39pt; BACKGROUND-COLOR: gray" width=115 height=52>Estimated Task Due Date</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: gray" width=107>Enter Revised Action Date</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: gray" width=105>Dependency Re-Calc Date</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: gray" width=94># of Days Difference</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=421 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=115 height=34 x:num="39668">8/8/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107 x:num="39666">8/6/2008</TD><TD class=xl27 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696 0.5pt solid; BORDER-LEFT: #969696; WIDTH: 79pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=105></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94 x:num>2</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=115 height=51 x:num="39671">8/11/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107 x:num="39667">8/7/2008</TD><TD class=xl27 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696; WIDTH: 79pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=105></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94 x:num>4</TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 24.95pt; BACKGROUND-COLOR: transparent" width=115 height=33 x:num="39665">8/5/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107></TD><TD class=xl27 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696; WIDTH: 79pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=105></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 24.95pt; BACKGROUND-COLOR: transparent" width=115 height=33 x:num="39666">8/6/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107></TD><TD class=xl34 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696; WIDTH: 79pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: transparent" width=105></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94></TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 24.95pt; BACKGROUND-COLOR: transparent" width=115 height=33 x:num="39682">8/22/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107 x:num="39675">8/15/2008</TD><TD class=xl35 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" width=105 x:num="39688">8/28/2008</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94 x:num>7</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=115 height=40 x:num="39668">8/8/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=105 x:num="39674">8/14/2008</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94></TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=115 height=40 x:num="39682">8/22/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107 x:num="39675">8/15/2008</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=105 x:num="39688">8/28/2008</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94 x:num>7</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD class=xl30 style="BORDER-RIGHT: #969696 1.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #969696 1.5pt solid; WIDTH: 86pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=115 height=40 x:num="39668">8/8/2008</TD><TD class=xl28 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #969696; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #969696 0.5pt solid; BACKGROUND-COLOR: silver" width=107></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=105 x:num="39674">8/14/2008</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94></TD></TR></TBODY></TABLE>
The problem I am running into is when it comes time to compare more than 2 cells of dates. If you look to the left of the cell highlighted in yellow you will see a date of 8/15. Two lines below the 8/15 shows again. I need the formula to reflect when the dates equal to take the difference of 7 only once. Hopefully this is making sense...
Any help any one can provide is greatly appreciated! I've spent hours on this already trying to figure it out.