Difference between two times

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

John,

Change the formula to

+B4-A4

and use a format of [h]" hours and" mm "mins"
(FormatCellsCustom)

including the total cell

--
HTH

Bob Phillips

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

"Jester" wrote in message
...
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

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"
--
Kevin Backmann


"Jester" wrote:

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

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

"Kevin B" wrote:

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"
--
Kevin Backmann


"Jester" wrote:

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

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


"Bob Phillips" wrote:

John,

Change the formula to

+B4-A4

and use a format of [h]" hours and" mm "mins"
(FormatCellsCustom)

including the total cell

--
HTH

Bob Phillips

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

"Jester" wrote in message
...
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