Welcome back to mrExcel Netcast, I am Bill John. Now today’s question is sent in by Greg. Greg is dealing with worksheet where he has some times and needs to figure out the difference between the two times, so normally we will just set up a little formula C-B and look at these results; 2:26:21 am. That is saying two hours and 46 minutes. Well that is not how Greg wants to show, he needs to show the number of hours and then a decimal point and the fractional number of hours. So this might be 2.76 hours or something like that. The way that Excel’s stores time, is it is a decimal between zero and one, so for example six hours would be 0.25 because it is 25% of a day.
So the solution here is to take that subtraction and just multiply the whole thing times 24. Now we are going to get the wrong answer here. We need to go in and use Format Cells; I will press Ctrl+1 and switch us back to a number with how are many decimal places you would like to see, so I will get a one decimal place.
And I will click Ok and you see that we now have an answer and when we copy that down, the number and the formatting we can actually see the decimal number of hours. The nice thing about this is it is easy to total up even if it is in excess of 24 hours because we are actually dealing with integers now and we do not have that problem where Excel cuts off the number of hours above 24 and you have to use the secret number format.
So now using this multiplying the time calculation by 24 to convert to hours, a great way to go, I think it will solve Greg’s problem. Well I want to thank you for stopping by; I will see you next time for another Netcast from mrExcel.
Transcription by:
Scribe4you Transcription Services