Hours and Minutes between two dates and times

[Ebusua]

Hi,

I am after a formula or VBA, which works out the total hours and minutes between two dates and times:
i.e. A1: 11/11/2014 21:00 A2: 12/11/2014 06:00.

Essentially I need the total hours and minutes between Cells A1 and A2.
Excel has difficulties with 24 hour periods and formulas I have tried place the total hours/minutes worked into a negative value if an end date goes into a 24 hour period.

I hope this makes sense,

Thanks

Ebusua

 

[adam087]

Welcome to the board!

Perhaps try something like this?

Excel 2010

	A	B

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]11/11/2014 21:00[/TD]
[TD="align: center"]12/11/2014 06:30[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"]9.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Minutes[/TD]
[TD="align: center"]570[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Combined[/TD]
[TD="align: center"]9 hours 30 minutes[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=(B1-A1)*(24)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=(B1-A1)*(24*60)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=INT((B1-A1)*24)&" hours "&ROUND(MOD((B1-A1)*(24*60),60), 0)&" minutes"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

/AJ

 

[Ebusua]

AJ, you're a star, this worked perfectly.

Thanks very much for your quick response

 

[adam087]

No problem. Thanks for the feedback

/AJ