Elapsed Time Formula Help & Setup ...

[Ken ...]

Excel ... 2000 ... I posted yesterday under General ?'s,
but did not get desired response. That said ... I am
hoping to clarify needs here by providing more detail &
actual data.

Cols A-B-C-D contain my data (described below)

Col E ... leave Blank
Col F ... unique S/N ... (I know how to do this)
Col G ... Formula for last (latest) time for unique S/N
Col H ... Formula for 1st (earliest) time for unique S/N
Col I ... leave Blank
Col J ... Formula to calculate diff between latest/
earliest time (format minutes) for each unique S/N

Col A Col B Col C Col D
(Date) (Time) (Time) (S/N)

06/25/04 0663 6:37 AM 1111111
06/25/04 1010 10:06 AM 1111111
06/25/04 1171 11:42 AM 1111111
06/24/04 1383 1:49 PM 2222222
06/24/04 2345 11:27 PM 2222222
06/25/04 0796 7:57 AM 2222222
06/25/04 0673 6:43 AM 3333333
06/25/04 1367 1:40 PM 3333333
06/25/04 1445 2:27 PM 3333333
06/01/04 1675 4:45 PM 4444444
06/01/04 1852 6:31 PM 4444444
06/01/04 2019 8:11 PM 4444444
06/01/04 2046 8:27 PM 4444444
06/01/04 2225 10:15 PM 4444444
06/07/04 1183 11:49 AM 4444444
06/07/04 1509 3:05 PM 4444444
06/08/04 2173 9:43 PM 4444444
06/09/04 0095 12:57 AM 4444444

My "Thanks" to the many Excel Wizards that support this
board ... Kha

 

[Bernie Deitrick]

Ken,

In E2, use the formula
=A2+C2
and copy down to match your data.

In F2, use the formula
=IF(COUNTIF($D$12,D2)= 1, "Unique","Not Unique")
and copy down to match your data.

In G2, array enter the formula (using Ctrl-Shift-Enter)
=IF(F2="Unique",MAX(($E$2:$E$XXX)*($D$2:$D$XXX=D2)),"")
Replace the XXX's with the row number of your last row of data.
And copy down to match your data.

In H2, array enter the formula (using Ctrl-Shift-Enter)
=IF(F2="Unique",MIN(IF($D$2:$D$XXX=D2,$E$2:$E$XXX,MAX($E$2:$E$XXX))),"")

Replace the XXX's with the row number of your last row of data.
And copy down to match your data.

In cell I2, use the formula
=INT(G2-H2) & " Days, " & TEXT((G2-H2)-INT(G2-H2),"hh:mm:ss")
and copy down to match your data.

HTH,
Bernie
MS Excel MVP