If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Difference between two times
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 |
#3
|
|||
|
|||
Difference between two times
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 |
#4
|
|||
|
|||
Difference between two times
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 |
#5
|
|||
|
|||
Difference between two times
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 |
Thread Tools | |
Display Modes | |
|
|