new to excel and need help

[teddyT]

Hi,

I guess this ? is to simple for this forum but I could sure use some help.

I have a table with the following:

start time end time elapsed time
<TABLE style="WIDTH: 153pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=255 x:str><COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=95><COL style="WIDTH: 48pt" span=2 width=80><TBODY><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 57pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 width=95 align=right x:num="0.71875">5:15 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 width=80 align=right x:num="0.78125">6:45 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 width=80 align=right x:num="6.25E-2" x:fmla="=+B1-A1">1:30</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.5625">1:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.66666666666666663">4:00 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="0.10416666666666663" x:fmla="=+B2-A2">2:30</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.69791666666666663">4:45 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.80208333333333337">7:15 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="0.10416666666666674" x:fmla="=+B3-A3">2:30</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.54861111111111116">1:10 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.63194444444444442">3:10 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="8.3333333333333259E-2" x:fmla="=+B4-A4">2:00</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.71527777777777779">5:10 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.77777777777777779">6:40 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="6.25E-2" x:fmla="=+B5-A5">1:30</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.72916666666666663">5:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.78125">6:45 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="5.208333333333337E-2" x:fmla="=+B6-A6">1:15</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.64583333333333337">3:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.6875">4:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="4.166666666666663E-2" x:fmla="=+B7-A7">1:00</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.71875">5:15 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.84375">8:15 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="0.125" x:fmla="=+B8-A8">3:00</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.53125">12:45 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.625">3:00 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="9.375E-2" x:fmla="=+B9-A9">2:15</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.6875">4:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.77083333333333337">6:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="8.333333333333337E-2" x:fmla="=+B10-A10">2:00</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.70833333333333337">5:00 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.80208333333333337">7:15 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="9.375E-2" x:fmla="=+B11-A11">2:15</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.52083333333333337">12:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.58333333333333337">2:00 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="6.25E-2" x:fmla="=+B12-A12">1:30</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.6875">4:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.77083333333333337">6:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="8.333333333333337E-2" x:fmla="=+B13-A13">2:00</TD></TR><TR style="HEIGHT: 12.75pt" height=21 vAlign=bottom><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=21 align=right x:num="0.60416666666666663">2:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.6875">4:30 PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 align=right x:num="8.333333333333337E-2" x:fmla="=+B14-A14">2:00</TD></TR></TBODY></TABLE>

Now the problem I'm having is when I do a sum I want a total of hours and minutes but after I go over 24hrs it converts the time to days. For example in my figures if I leave off the last row it totals to 23:15 but by adding the last row of 2 hrs it totals to 1:15. I somehow want it to just show me a total of 25.15.

I hope I'm explaining this properly and thank anyone in advance for taking the time to read and maybe help me out.

 

[alansidman]

Format the totalled cell as xx:xx:xx under the Time Format

Excel Workbook
	O	P	Q	R	S	T
1	start time end time elapsed time
2	5:15 PM	6:45 PM	1:30
3	1:30 PM	4:00 PM	2:30
4	4:45 PM	7:15 PM	2:30
5	1:10 PM	3:10 PM	2:00
6	5:10 PM	6:40 PM	1:30
7	5:30 PM	6:45 PM	1:15
8	3:30 PM	4:30 PM	1:00
9	5:15 PM	8:15 PM	3:00
10	12:45 PM	3:00 PM	2:15
11	4:30 PM	6:30 PM	2:00
12	5:00 PM	7:15 PM	2:15
13	12:30 PM	2:00 PM	1:30
14	4:30 PM	6:30 PM	2:00
15	2:30 PM	4:30 PM	2:00
16			27:15:00	Format as Time xx:xx:xx
Sheet1

 

[teddyT]

Mr alansidman -- thank you very very much.

 

[scottylad2]

I'm not sure what the above format does in your country, but in my Time column in the Format dialogue box there's no place to input xx:xx:xx the settings in there are predetermined. In the custom format, it displays exactly whats typed entering the above...maybe a setting local yo you?

the normal format for calculating acumulated time is
[h]:mm:ss

 

[teddyT]

Hi scottylad2,

If I use the format xx.xx.xx it totals to what I want but if I use the format [h]:mm:ss it doesn't.

Not sure why but I hope to figure that out.

Thanks for responding but Mr alansidman solved my problem.

 

[alansidman]

Scottylad2: FYI this is how it looks on my PC

 

[teddyT]

Scottylad2: FYI this is how it looks on my PC

As a long time user of Lotus 123 changing over to Excel hasn't been to bad but I've come to appreciate some things in Excel like this formatting into the 37:30:55 format shown above which isn't available in L123. If you'll note it says 37 for the hours which is more than 24 so when it totals up my elapsed time column it gives the correct number of hours. I just couldn't get this in Lotus 123 because there is no format option like 37:30:55. Thanks to Mr alansidman and his excellent help I'm back on track again.

 

[scottylad2]

Hi Alansidman & teddy, thanks for getting back. I'm not seeing the image, but I'm guessings it's a format peculiar to lotums or similar, had a bash at recreating in 2010 but only displayed what I typed xx:xx;xx

Every days a school day

 

[teddyT]

Hi scottylad2,

I don't know what happened to the image but where it used to be it now says:

Anyways -- Have a great day !!!