Tricky Date Calculation

[dan]

Hi, I have a spreadsheet similiar to the one below. Lets call it
Sheet1. Using an excel macro, on a new sheet, lets call it Sheet2, I
want the first row to have the amount of time between start and stop
(stop-start) for the value 20, and to copy the value in column D. So
sheet 2 might look like:
Sheet 2
Col A Col B Col C
20 13 days Dog

Notice that sometimes stop is listed on the row before start, and
sometimes start is before stop. Also, sometimes there is only a start
or a stop for a value in A, bur not both - in this case, I'd like to
ignore that set.

Is there some way to say something like ColB.Stop - ColB.Start WHERE
ColA = ColA or something like that??


Sheet 1
Col A Col B Col C Col D
20 Start 5/17/2006 6:26:58 AM Dog
20 Stop 5/30/2006 11:54:33 AM Dog
30 Stop 5/28/2006 11:26:12 AM Cat
30 Start 5/20/2006 1:54:32 AM Cat
32 Stop 5/22/2006 5:56:38 AM Horse

Thank you soooo much!!!!
Dan

 

[James Walker]

How about using only one sheet and no macro?

Sheet 1
Col A Col B Col C Col D (Start)
Col E (Stop)
20 (see forula below) Dog 5/17/2006 6:26:58 AM
5/30/2006 11:54:33 AM
30 (fill with formula) Cat 5/20/2006 1:54:32 AM
5/28/2006 11:26:12 AM
32 (fill with formula) Horse 5/22/2006 5:56:38 AM

formula: =IF(OR(D1="",E1=""),"",IF(E1<D1,"STOP date is before START
date",ROUND(E1-D1,0)&" days"))

Will this work for you?

James Walker, Jr.

 

[Bob Phillips]

Number of days

=IF(OR(ISNA(MATCH(1,(Sheet1!A1:A5=A1)*(Sheet1!B1:B5="Start"),0)),ISNA(MATCH(
1,(Sheet1!A1:A5=A1)*(Sheet1!B1:B5="Stop"),0))),"",
INDEX(Sheet1!C1:C5,MATCH(1,(Sheet1!A1:A5=A1)*(Sheet1!B1:B5="Stop"),0))
-INDEX(Sheet1!C1:C5,MATCH(1,(Sheet1!A1:A5=A1)*(Sheet1!B1:B5="Start"),0)))

animal

=IF(OR(ISNA(MATCH(1,(Sheet1!A1:A5=A1)*(Sheet1!B1:B5="Start"),0)),ISNA(MATCH(
1,(Sheet1!A1:A5=A1)*(Sheet1!B1:B5="Stop"),0))),"",
INDEX(Sheet1!D15,MATCH(1,(Sheet1!A1:A5=A1)*(Sheet1!B1:B5="Start"),0)))

which are both array formulae, it should be committed with Ctrl-Shift-Enter,
not just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)