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 string like" 120 Hours and 35 Minutes "

Any help would be greatly appreciated

Many Thanks

John - Sydney AUSTRALIA

When you applied:
= (B4-A4)*24

you converted the time differences into numbers scaled such that 1.1 means
one and one tenth hours. Once you have added up a column of these (say in
column C) with the sum in C100, then in another cell enter:

=C100/24 and format this cell as Custom hhh:mm
--
Gary's Student


"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 string like" 120 Hours and 35 Minutes "

Any help would be greatly appreciated

Many Thanks

John - Sydney AUSTRALIA

For the cell you want to change the result to 14 Hours and 30 minutes, change
the format to custom then type in at the top:-
h "Hours and" mm "Minutes" which, if the cell states 440:15 before will
display
440 Hours and 15 Minutes

Hope that helps

--
Dave


"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 string like" 120 Hours and 35 Minutes "

Any help would be greatly appreciated

Many Thanks

John - Sydney AUSTRALIA

Use a format of [h]" hours and" mm "mins"

(See replies to your identical question on 31st August ("Difference between
two times")


"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 string like" 120 Hours and 35 Minutes "

Any help would be greatly appreciated

Many Thanks

John - Sydney AUSTRALIA