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

[TABLE="width: 385"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Arrivel Time[/TD]
[TD]Departure Time[/TD]
[TD]Total hrs int[/TD]
[TD]Total hrs ext[/TD]
[/TR]
[TR]
[TD]13/12/2018 14:29[/TD]
[TD]13/12/2018 15:49[/TD]
[TD]1h 20min[/TD]
[TD]1h 20min[/TD]
[/TR]
[TR]
[TD]13/12/2018 14:21[/TD]
[TD]13/12/2018 16:08[/TD]
[TD]1h 46min[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/12/2018 12:54[/TD]
[TD]13/12/2018 14:29[/TD]
[TD]1h 34min[/TD]
[TD]1h 34min[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Zenwood]

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

[TABLE="width: 872"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=LEFT(A2,10)[/TD]
[TD]=RIGHT(A2,5)[/TD]
[TD]=LEFT(B2,10)[/TD]
[TD]=RIGHT(B2,5)[/TD]
[TD]=H2-F2[/TD]
[/TR]
[TR]
[TD]=LEFT(A3,10)[/TD]
[TD]=RIGHT(A3,5)[/TD]
[TD]=LEFT(B3,10)[/TD]
[TD]=RIGHT(B3,5)[/TD]
[TD]=H3-F3[/TD]
[/TR]
[TR]
[TD]=LEFT(A4,10)[/TD]
[TD]=RIGHT(A4,5)[/TD]
[TD]=LEFT(B4,10)[/TD]
[TD]=RIGHT(B4,5)[/TD]
[TD]=H4-F4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUM(I2:I4)[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 705"]
<colgroup><col span="2"><col><col><col span="5"></colgroup><tbody>[TR]
[TD]Arrivel Time[/TD]
[TD]Departure Time[/TD]
[TD]Total hrs int[/TD]
[TD]Total hrs ext[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/12/2018 14:29[/TD]
[TD]13/12/2018 15:49[/TD]
[TD]1h 20min[/TD]
[TD]1h 20min[/TD]
[TD]43447.6035[/TD]
[TD]69444[/TD]
[TD]43447.6596[/TD]
[TD]96296[/TD]
[TD]26852[/TD]
[/TR]
[TR]
[TD]13/12/2018 14:21[/TD]
[TD]13/12/2018 16:08[/TD]
[TD]1h 46min[/TD]
[TD][/TD]
[TD]43447.5985[/TD]
[TD]69444[/TD]
[TD]43447.6725[/TD]
[TD].6725[/TD]
[TD="align: right"]-69443.3[/TD]
[/TR]
[TR]
[TD]13/12/2018 12:54[/TD]
[TD]13/12/2018 14:29[/TD]
[TD]1h 34min[/TD]
[TD]1h 34min[/TD]
[TD]43447.5376[/TD]
[TD]50463[/TD]
[TD]43447.6035[/TD]
[TD]69444[/TD]
[TD="align: right"]18981[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-23610.3[/TD]
[/TR]
</tbody>[/TABLE]

 

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