Elapsed Time Counting Techniques

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

Elapsed Time

 ABCDEF
 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.

For more information on use of Fill Handle to copy down
  http://www.mvps.org/dmcritchie/excel/fillhand.htm

For more information on Date and Time «
  http://www.mvps.org/dmcritchie/excel/datetime.htm

For more information on use of OFFSET
  http://www.mvps.org/dmcritchie/excel/offset.htm

For more information on SUMIF and COUNTIF «

  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).
This page was introduced on May 24, 2004. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved