Skip to content

Calculate Hours With a Spreadsheet

Do you ever get stuck trying to do “time math” in your head?

For instance, have you ever found yourself in a situation where you need to know how many hours there are between 8:43 AM and 11:02 AM? Such as when you need to input your work hours for the morning.

What about when you are trying to make sure that you get two hours of reading in for the day? Do you find yourself trying to plan out multiple reading sessions that eventually add up to two hours in total, yet you just don’t have the right tool for the job?

Well my friend, I suggest that you capitalize on the power of spreadsheets!

Spreadsheets Are Great for “Time Math”

A spreadsheet is great when it comes to doing all kinds of math, and that includes “time math” too!

To demonstrate, I am going to show you how to turn any spreadsheet into the perfect “time math” companion.

The first thing you need is a good starting point for your spreadsheet. I suggest setting up your spreadsheet to look like this:

If you aren’t sure how to get your spreadsheet to look like this, you can download the starting template below and go from there.

Hour Calculator – Start

What Your Will Learn

I am going to teach you how to write and use a specific formula that will make it possible for you to calculate the total amount of hours between any two times.

Once you have mastered this formula, you will be able to re-make any spreadsheet into your own personal “hour calculator.”

After I teach you this formula, I will show you how to add the formula to your spreadsheet and then duplicate it so that you can add up all the hours between several different time intervals.

Finally, I’ll show you what the final product should look like so that you end the day with a fully functional and easy to use hour calculator spreadsheet. So without further ado, let’s get to it!

Step No. 1 – Write the Formula

We will be using one main formula to calculate the total amount of hours between two times. The formula is shown below:

=(HOUR(D7)-HOUR(D6))+((MINUTE(D7)-MINUTE(D6))/60)

You will enter this formula into cell D8 in the starting template as shown below.

Calculate Hours With A Spreadsheet - The Formula

If you would like to test out the formula to see if it is working properly, enter the value of 8:30 AM into cell D6 and the value of 10:15 AM into cell D7. You should see the value of 1.75 appear in cell D8 (which contains our formula).

Calculate Hours With A Spreadsheet - The Times

If you would like to understand how the formula works, keep on reading. If you would instead like to move along to what comes next in the spreadsheet, feel free to jump to step 2.

How the Formula Works

This formula calculates the difference of hours between two times. To understand how it does this, we will look at the two main parts of the formula.

First, let’s look at the first portion of the formula.

=(HOUR(D7)-HOUR(D6))

This part of the formula calculates the hour value of cell D7 and subtracts it from the hour value of D6. So, in the case that D7 contains the value of 10:15 AM and D6 contains the value of 8:30 AM and, the formula would result in the following:

=(10-8)

This of course ends up being equal to 2.00.

Now let’s look at the second part of the formula.

((MINUTE(D7)-MINUTE(D6))/60)

This part of the formula calculates the minute value of cell D7 and subtracts it from the minute value of D6. It then takes this result and divides it by 60.

We do this because there are 60 minutes in 1 hour, and we want our final value to be in hours.

So in the case that D7 contains the value of 10:15 AM and D6 contains the value of 8:30 AM, the formula would result in the following:

((15-30)/60)

This of course ends up being equal to -0.25.

Finally, when we put the two pieces of the formula together, we get this:

(2.00)+(-0.25) = 1.75

And this is how we end up with our formula calculating that there are 1.75 Hours in between 8:30 AM and 10:15 AM.

Step No. 2 – Copy and Paste

You should now have a fully functional hour calculator widget.

To make our spreadsheet even more powerful, we are going to copy and paste our widget several times. This way we will be able to calculate the hours between several different sets of times.

Calculate Hours With A Spreadsheet - Copy and Paste Widget

Plus, the best part is, we won’t have to write the same hour calculation formula over and over again. Since we used relative references (D6 & D7) instead of absolute references ($D$6 & $D$7) , the formula at the bottom of each individual widget will always reference the two cells above it.

So go ahead and copy your widget and paste it five times. Your final product should look something like this.

Calculate Hours With A Spreadsheet - The Widget Pasted

Step No. 3 – Add Up the Hours

Now that we have six hour calculator widgets, all we have to do is add up the six cells with hour totals in them to get our overall total.

To do this, add the following formula to cell D4.

=D8+D12+D16+G8+G12+G16

This formula simply takes the values in the six referenced cells (D8, D12, D16, G8, G12, G16) and adds them all up.

Calculate Hours With A Spreadsheet - Summing Up the Hours

And with that, we have our total hour calculator functioning at full capacity.

If you’d like to add a title to your spreadsheet, simply select cell C2 and enter in a creative name like Hour Calculator or something like that.

Calculate Hours With A Spreadsheet - Adding a Title

Now you finally have a fully functioning easy to use spreadsheet to help you with all that complicated “time math”.

If you weren’t able to get your spreadsheet to work, feel free to leave me a comment with any questions. You can also download the completed spreadsheet by clicking the link below.

Hour Calculator – End

Until next time, have fun crunching those times with your brand new spreadsheet!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top