Skip to content

Time Math in Spreadsheets

Have you ever tried building a timesheet or something similar in Microsoft Excel or Google Sheets? It’s not as trivial as you might first imagine.

You would think that you could enter the start and stop times and then subtract them to get a result. The problem is that you would get back a number you probably were not expecting.

How Time Math Works in Spreadsheets

Let’s say that you’ve entered the following start and stop times into your spreadsheet.

Now, what happens when you try to calculate the difference between the two times to determine the duration in hours?

You end up with a result that doesn’t seem to make sense.

Why Does This Happen?

You end up with a value you probably were not expecting because spreadsheets treat dates and times like regular old numbers.

For example, Google Sheets interprets the time of 3:00 PM as the number 0.625.

However, due to the number formatting applied to cell C2, the number shows up as the time value of 3:00 PM.

So How Do You Calculate the Duration?

To calculate the duration, you must first understand how a spreadsheet relates time values to numerical values. The relationship is actually quite simple.

The number 0 represents the time 00:00 or 12:00 AM, and the number 1 represents the end of the day or 24:00. This explains why 3:00 PM is equivalent to the numerical value of 0.625.

3:00 PM is the 15th hour of the day, and there are 24 hours each day. The number 15 divided by 24 is equivalent to 0.625.

Using this knowledge of how spreadsheets relate times and numbers, use the formula below to calculate the duration between two times in the form of hours.

= ( stop_time - start_time ) * 24

The formula above first takes the start and stop times and subtracts them to calculate a numerical value representing a fraction of one day.

The second part of the formula takes this fraction and multiplies it by 24 to get the final duration in the form of hours.

Calculate the Duration in Minutes

If you want to calculate the duration in minutes rather than hours, take your previous formula and add a multiplication by 60 to the end.

= ( stop_time - start_time ) * 24 * 60

This works because there are 60 minutes in every hour.

And that about sums up how to do simple time math in spreadsheets to calculate duration. Now you will be prepared the next time you have to work with hours and minutes in spreadsheets.

This Post Has 2 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