Difference between dates minus DOWN time.

[wsautrey]

I have an Excel 2003 sheet with 2 columns. Column A contains dates in ascending order. It may also contain "DOWN" between the dates. I need a formula to subtract the previous date from the newer date and subtract the number of occurrences of "DOWN" that may occur between the dates. I need to be able to drag this formula down the length of the sheet to calculate the difference between the next date and the previous again subtracting the ocurrences of "DOWN". Data begins in cell A32. Similar to below.

<table style="width: 381px; height: 451px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 89pt;" width="118"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 89pt;" width="118" height="20">Hurt DATE</td> <td class="xl65" style="width: 48pt;" width="64">Stretch</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">1/1/06 5:00 AM</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">1/4/06 2:00 PM</td> <td class="xl66" align="right">3.375</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">Down</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">1/8/06 5:00 AM</td> <td class="xl66" align="right">2.625</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">Down</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">Down</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">Down</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">1/16/06 8:00 AM</td> <td class="xl66" align="right">5.125</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">
</td> <td class="xl66">
</td> </tr> </tbody></table>

 

[Andrew Fergus]

Hi

How about something like this:

   A               B       C                       
 1 Hurt Date       Stretch Helper                  
 2 1/01/2006 5:00                                  
 3 4/01/2006 14:00 3.375                        2  
 4 Down                                         3  
 5 8/01/2006 5:00  2.625                        3  
 6 Down                                         5  
 7 Down                                         5  
 8 Down                                         5  
 9 16/01/2006 8:00 5.125                        5  
10 Down                                         9  
11 Up                                           9  
12 Down                                         9  
13 20/01/2006 9:00 2.042                        9  
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
B3:B13  =IF(ISNUMBER(A3),A3-INDEX(A$1:A2,C3)-SUMPRODUCT((A$1:A2="Down")*(C$1:C2=C3)),"")
C3:C13  {=MAX(ISNUMBER(A$2:A2)*ROW(A$2:A2))}
{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down
[Table-It] version 09 by Erik Van Geit

Notice it uses a helper column in column C which is an array formula (see instructions per my example). Copy the formulas in columns B and C down as far as you like. This assumes the data starts in row 2 with a header in row 1. Notice also the first row of formulas is row 3.

Andrew

P.S. My dates are English format which may appear backwards compared to yours.