# Elapsed Time Counting Techniques

Location: http://www.mvps.org/dmcritchie/excel/elapsed.htm
[View without Frames]

## Elapsed Time

 A B C D E F 1 Date------ Start End-- Elapsed ByDate Counts 2 2004-05-01 08:25 09:30 01:05 04:37 3 3 2004-05-01 10:55 13:20 02:25 ------- ------- 4 2004-05-01 14:13 15:20 01:07 ------- ------- 5 2004-05-02 08:25 09:30 01:05 03:30 2 6 2004-05-02 10:55 13:20 02:25 ------- ------- 7 2004-05-03 14:13 15:20 01:07 01:07 1 8 Total 09:14
```D2:  =C2-B2+(B2>C2)
E2:  =IF(COUNTIF(A\$2:A2,A2)=1,SUMIF(A:A,A2,D:D),"-------")
F2:  =IF(COUNTIF(A\$2:A2,A2)=1,COUNTIF(A:A,A2),"-------")
E8:  =SUM(E2:OFFSET(E8,-1,0))

format of D2:E7  is  [hh]:mm
```
The logical expression (B2>C2) in cell D2 adds 1 day (24 hours) if the start time is greater than the End time.

format of D2:E7 is [hh]:mm
the brackets around h or hh prevent hours from
overflowing into days. Time is a fraction of a day.

http://www.mvps.org/dmcritchie/excel/fillhand.htm

http://www.mvps.org/dmcritchie/excel/datetime.htm

http://www.mvps.org/dmcritchie/excel/offset.htm

COUNT, Tip 52:Cell Counting Techniques
http://www.j-walk.com/ss/excel/tips/tip52.htm

Summing and Counting Using Multiple Criteria (tip 74)
http://www.j-walk.com/ss/excel/tips/tip74.htm

Count and Sum Your Data in Excel 2002 (also by John Walkenbach)
http://www.microsoft.com/office/previous/xp/columns/column10.asp

Excel Function Dictionary « by Peter Noneley is a reference to definitely download and maintain on your computer, the workbook has 157+ sheets each with an explanation and example of an Excel function.

Use of first and last name in these newsgroups would be appreciated.

See SUMIF Example

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).