Convert Hr M to time

[taffjohn]

Hi

I have the following information of contractors of internal and external staff and when the sheet comes to me i get thousands of lines but i need to total the columns up into Days, Hours,Minutes but i need a little help with converting the cells because there is so much i need to change the way the cell shows its info, ive attached file to show my problem

Arrivel Time	Departure Time	Total hrs int	Total hrs ext
13/12/2018 14:29	13/12/2018 15:49	1h 20min	1h 20min
13/12/2018 14:21	13/12/2018 16:08	1h 46min
13/12/2018 12:54	13/12/2018 14:29	1h 34min	1h 34min
Total		?	?

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[Zenwood]

Try this in columns E,F,G,H and I.

=LEFT(A2,10)	=RIGHT(A2,5)	=LEFT(B2,10)	=RIGHT(B2,5)	=H2-F2
=LEFT(A3,10)	=RIGHT(A3,5)	=LEFT(B3,10)	=RIGHT(B3,5)	=H3-F3
=LEFT(A4,10)	=RIGHT(A4,5)	=LEFT(B4,10)	=RIGHT(B4,5)	=H4-F4
				=SUM(I2:I4)

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[gaz_chops]

Maybe try using a custom format, like DD hh:mm

[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl63, width: 171"]Arrivel Time[/TD]
[TD="class: xl63, width: 171"]Departure Time[/TD]
[TD="class: xl63, width: 115"]Total hrs int[/TD]
[TD="class: xl63, width: 119"]Total hrs ext[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]13/12/2018 14:29[/TD]
[TD="class: xl64, align: right"]14/12/2018 15:49[/TD]
[TD="class: xl65, align: right"]01 01:20[/TD]
[TD="class: xl65, align: right"]01 01:20[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]13/12/2018 14:21[/TD]
[TD="class: xl64, align: right"]13/12/2018 16:08[/TD]
[TD="class: xl65, align: right"]00 01:47[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]13/12/2018 12:54[/TD]
[TD="class: xl64, align: right"]13/12/2018 14:29[/TD]
[TD="class: xl65, align: right"]00 01:35[/TD]
[TD="class: xl65, align: right"]00 01:35[/TD]
[/TR]
[TR]
[TD="class: xl63"]Total[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65, align: right"]01 04:42[/TD]
[TD="class: xl65, align: right"]01 02:55[/TD]
[/TR]
</tbody>[/TABLE]

 

[taffjohn]

this is what it came back with

Arrivel Time	Departure Time	Total hrs int	Total hrs ext
13/12/2018 14:29	13/12/2018 15:49	1h 20min	1h 20min	43447.6035	69444	43447.6596	96296	26852
13/12/2018 14:21	13/12/2018 16:08	1h 46min		43447.5985	69444	43447.6725	.6725	-69443.3
13/12/2018 12:54	13/12/2018 14:29	1h 34min	1h 34min	43447.5376	50463	43447.6035	69444	18981
Total		?	?					-23610.3

<colgroup><col span="2"><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[taffjohn]

Is there any way i could attach my excel file i have and you might be able to work it out easier?

 

[gaz_chops]

To get your Total Hours etc you simply need to say Dep Time minus Arr Time, then sum for Totals and Format cells as I said above.

Check my sig for options to post a sample of data.