Calculate time difference and show the result as a decimal number of hours. Episode 902 will show you how.
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Now, today's question’s sent in by Greg.
Greg is dealing with a worksheet where he has some times, and needs to figure out the difference between the two times.
And so normally, we would just set up a little formula-- C - B. And look at this result 2:26:21 AM; that's saying 2 hours and 46 minutes-- 46 minutes.
Well, that's not how Greg wants to show it-- he needs to show the number of hours and then a decimal point and the fractional number of hours.
So, you know, this might be 2.76 hours or something like that.
The way that Excel stores time is, it's a decimal between 0 and 1.
So for example, 6 hours would be 0.25 because it's 25% of a day.
So the solution here, is to take that subtraction and just multiply the whole thing * 24.
Now, we're going to get the wrong answer here; we need to go in and use Format Cells.
I press Ctrl+1 and switch this back to a number with however many decimal places you'd like to see.
So I'll go to one decimal place, will click OK, and you see that we now have an answer.
And when we copy that down-- the number of the formatting-- we can actually see the decimal number of hours.
The nice thing about this is it's easy to total up, even if it's in excess of 24 hours, because we're actually dealing with integers.
Now, we don't have that problem where Excel cuts off the number of hours above 24 and you have to use the secret number format.
So using this, multiplying the time calculation by 24 to convert to hours, great way to go.
I think that'll solve Craig's problem.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Now, today's question’s sent in by Greg.
Greg is dealing with a worksheet where he has some times, and needs to figure out the difference between the two times.
And so normally, we would just set up a little formula-- C - B. And look at this result 2:26:21 AM; that's saying 2 hours and 46 minutes-- 46 minutes.
Well, that's not how Greg wants to show it-- he needs to show the number of hours and then a decimal point and the fractional number of hours.
So, you know, this might be 2.76 hours or something like that.
The way that Excel stores time is, it's a decimal between 0 and 1.
So for example, 6 hours would be 0.25 because it's 25% of a day.
So the solution here, is to take that subtraction and just multiply the whole thing * 24.
Now, we're going to get the wrong answer here; we need to go in and use Format Cells.
I press Ctrl+1 and switch this back to a number with however many decimal places you'd like to see.
So I'll go to one decimal place, will click OK, and you see that we now have an answer.
And when we copy that down-- the number of the formatting-- we can actually see the decimal number of hours.
The nice thing about this is it's easy to total up, even if it's in excess of 24 hours, because we're actually dealing with integers.
Now, we don't have that problem where Excel cuts off the number of hours above 24 and you have to use the secret number format.
So using this, multiplying the time calculation by 24 to convert to hours, great way to go.
I think that'll solve Craig's problem.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
