Calculating Time

  • Thread starter Thread starter bagia
  • Start date Start date
B

bagia

I've created a spreadsheet with room usage. You will
notice that Column D is the time when the room started to
be used by each student, and column E is the time that
they finished using the room. How can I create a
function to created elapsed time for each date? Also, I
need to calculate a total for the time elapsed.

ColB ColC ColD ColE(time elapsed)
5/1/2003 8:25 AM 9:30 AM ?
5/1/2003 10:55 AM 1:20 PM ?
5/1/2003 2:13 PM 3:20 PM ?

Total time elapsed ?


Any suggestions or advice would be greatly appreciated.

Thanks!
 
Hi
in E1: =D1-C1
also just sum column E and format the resulting cell with the custom
format
[hh]:mm
 
Thank you very much Frank!
-----Original Message-----
Hi
in E1: =D1-C1
also just sum column E and format the resulting cell with the custom
format
[hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany

bagia said:
I've created a spreadsheet with room usage. You will
notice that Column D is the time when the room started to
be used by each student, and column E is the time that
they finished using the room. How can I create a
function to created elapsed time for each date? Also, I
need to calculate a total for the time elapsed.

ColB ColC ColD ColE(time elapsed)
5/1/2003 8:25 AM 9:30 AM ?
5/1/2003 10:55 AM 1:20 PM ?
5/1/2003 2:13 PM 3:20 PM ?

Total time elapsed ?


Any suggestions or advice would be greatly appreciated.

Thanks!
Click to expand...

.
Click to expand...
 
HI "bagia" <[email protected]>

-----A----- ---B--- ---C--- ---D--- ---E---
Date--- Start End-- Elapsed ByDate
5/1/2003 08:25 09:30 01:05 04:37
5/1/2003 10:55 13:20 02:25 -------
5/1/2003 14:13 15:20 01:07 -------
5/2/2003 08:25 09:30 01:05 03:30
5/2/2003 10:55 13:20 02:25 -------
5/4/2003 14:13 15:20 01:07 01:07
Total 09:14

D2: =C2-B2+(B2>C2)
E2: =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A:A,A2,D:D),"-------")
E8: =SUM(E2:OFFSET(E8,-1,0))

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/excel.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

Recommended download:
Excel Function Dictionary by Peter Noneley
-- http://homepage.ntlworld.com/noneley
workbook with 157+ sheets, each with an explanation and example of an Excel function.

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