Difference between two times

[Jester]

I have a timesheet and I have a simple formula that calculates the diff
between 2 times and goes something like this:

= (B4-A4)*24

Where B4 = 4:45 PM and A4 = 7:15 AM


The result (formatted correctly) is 9.50 Hrs


I can continue dong this row after row and eventually total it at the bottom
and get something like 440.15 Hrs


So what's my question? What happens if I want this formula modified so that
the result can be expressed as something like :

9 Hours and 30 Minutes

Can someone help? (Pls bear in mind that whatever the answer I still need
to be able to go to the bottom of all the calculated differences and add up
a total, so as to end up with a result like" 120 Hours and 35 Minutes "

Any help would be greatly appreciated

Many Thanks

John - Sydney AUSTRALIA

 

[Bob Phillips]

John,

Change the formula to

+B4-A4

and use a format of [h]" hours and" mm "mins"
(Format>Cells>Custom)

including the total cell

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

You could use the formula below to format the result:

=INT((B4-A4)*24)&" Hours and "&ROUND((MOD((B4-A4)*24,1))*100,0)&" minutes"

 

[Guest]

Format cells as Custom==> [hh] " hours" mm " minutes"

 

[Guest]

Thanks to Toppers and Bob Phillips. Leave it to me to present the most labor
intensive method, I totally forgot about custom formats...

You guys keep me honest...
--
Kevin Backmann

John,

Change the formula to

+B4-A4

and use a format of [h]" hours and" mm "mins"
(Format>Cells>Custom)

including the total cell

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have a timesheet and I have a simple formula that calculates the diff
between 2 times and goes something like this:

= (B4-A4)*24

Where B4 = 4:45 PM and A4 = 7:15 AM


The result (formatted correctly) is 9.50 Hrs


I can continue dong this row after row and eventually total it at the bottom
and get something like 440.15 Hrs


So what's my question? What happens if I want this formula modified so that
the result can be expressed as something like :

9 Hours and 30 Minutes

Can someone help? (Pls bear in mind that whatever the answer I still need
to be able to go to the bottom of all the calculated differences and add up
a total, so as to end up with a result like" 120 Hours and 35 Minutes "

Any help would be greatly appreciated

Many Thanks

John - Sydney AUSTRALIA