Calculating hours

[want to learn]

Cant figure this out!

I have a column with a date (8/24/2007) and column with a start time in 24 hour format 16:50:37and a column with a stop time in 24 hour format 3:23:29. In the cells to the right I want the total number of hours:minutes:seconds from the time the event started till it stopped. As you can see the time crossed midnight.

Can someone help me with this formula?

 

[cpg84]

Have you tried to put both the date and time in the same cell? If you can, I will tell you what formula I used in a similar situation.

 

[want to learn]

Since there could be thousands of rows it would take alot of time to add the date to the time cell unless there is a formula for that as well. I can add the date to that cell to see the formula you use.

 

[cpg84]

Let's say A1 has the first date and B1 has the second date.

Number of hours(in C1): =ROUNDDOWN((B1-A1)*24,0)
Number of minutes(in D1): =ROUNDDOWN((B1-A1)*1440,0)-(C1*60)
Number of seconds(in E1): =ROUNDDOWN((B1-A1)*86400,0)-(C1*3600)-(D1*60)

I have no idea how to have the date and time together, except writing them in the same cell. I have only done this to a small group of numbers, rather than heaps and heaps of numbers.

 

[facethegod]

A1-start time B1-end time, code in C1

=TEXT(MOD((B1-A1)*24,24)/24,"[h]:mm:ss")

 

[cblincoln43]

Give this a try,
= b1 - a1 + ( b1< a1)

Format: h:mm:ss and remember the time must be entered with the 24 hour clock. 0:01:01 thru 23:59:01 and formated to what you want . OH! with this you do not need a DATE. I dug this sample out of the old box, give it a look,

new time sheet 2007.xls
	A	B	C	D	E	F	G	H	I	J	K	L
10		Sun.	Mon.	16:45	00:45	8.0
11	Time	2/4	2/5	4:45 PM	12:45 AM	8:00
12	Time Card			16.7	0.7	8.0	0.5		7.5	6.2	0.0	1.3
13
14		Mon.	Tue.	17:15	03:15	10.0
15	Time	2/5	2/6	5:15 PM	3:15 AM	10:00
16	Time Card			17.2	3.2	10.0	0.5		9.5	8.0	1.5	0.0
17
Sheet1

 

[Scott Huish]

=MOD(C1-B1,1)

 

[want to learn]

Thanks to all, I found what the combination that works.
Thank you Cammy84