Time Calculations

I was working on a timesheet in Excel the other day, and I was surprised to see that 4:00 PM minus 2:00 PM doesn't equal 2 hours. Very surprised indeed...

So, you know what I did? I hit F1. F1 and my husband and my friend Dawn are the three reasons I am the excel guru that I claim to be.

F1 was very helpful, but I took what it told me and expanded my new found knowledge to get it to do what I actually wanted my timesheet application to do. What I wanted my application to do was calculate the elapsed time in hours between a start and end time in one 24 hour period (as in, not to exceed a day).

Here's what I wanted to see:

From             To                 Elapsed Time in Hours
2:00 PM        4:00 PM             2

When I subtracted 2:00 PM from 4:00 PM like this =(B2-A2), here's what I got:
From             To                 Elapsed Time in Hours
2:00 PM        4:00 PM             .0833
When displayed as time, .0833 = 2:00 AM

So, I think I get what it's doing, but it's not what I want.

F1 suggested a few functions, including the hour function. So, I changed my formula to this:
=HOUR(B2-A2)

And that converted .0833 to the number 2. Which was precisely what I wanted in this particular instance.

So, I pulled my formula down, and I discovered that when my start time was 2:00 and me end time was 4:45, my result was still the number 2.

I hit F1 again, and finished reading the help article. It was then that I discovered the MINUTE function.

Using the minute function on this data set:
From To
2:00 PM 4:45 PM
=MINUTE(B2-A2)
Yields this result:
45

So, I put the two functions together in this simple formula:
=HOUR(B2-A2)+(MINUTE(B2-A2)/60)

It now shows me the difference in hours plus the difference in minutes divided by 60.

From                 To                 Elapsed time in hours
2:00 PM            4:45 PM              2.7500

Easy, peasy puddin' pie.

Just keep in mind, this will not work if the time spans more than 24 hours.