How to Calculate Time Range in Excel (4 Handy Methods)

This article will show you how to calculate the time range in Excel with a variety of methods. You will find a few useful functions and formulas to subtract times to calculate time difference or time range in Excel. Here, we will take you through 4 easy and convenient methods on how to calculate the time range in Excel.

Calculating the difference between two times in Excel might be tricky. Internally, time is represented as an integer between 0 and 1 in Excel. One day is equal to 1, which equals 24 hours. This means that times and hours are fractional representations of 1. For example, Excel considers 3 hours as a numerical value of 3/24 or 0.125.

We’ve got a dataset of Employee Time Sheet containing “In Time” and “Out Time” of 10 employees for a certain day.

So, we’ll calculate the time range from these times using different methods.

1. Calculating Simple Time Range in Excel

When the start and end times are on the same day, calculating the duration in hours is simple. In this method, we’ll determine the time range from Out Time which is greater than the In Time. For doing this, we intend to use some different functions and formulas.

1.1. Using Simple Subtraction

The first sub-method for this calculation is simple subtraction. This method involves subtraction of In Time from the Out Time.

Doesn’t it pique your interest? So, let’s dive into the steps below.

Steps:

• Firstly, select cell E5 and write down the formula as follows, and press ENTER.

It’s just a normal subtraction formula that operates on cells D5 and C5 to bring out the output in cell E5.

• As we can see the output in cell E5 shows 8:00 AM. But, we expect our time range just to have hours and minutes. So, now using the Fill Handle tool we’ll complete column E and then press Ctrl+1 to open Custom Format Cells option. Later, select h:mm from Type menu’s drop-down list and click on OK.

• Instantly, we can see our time range in hours and minutes format.

1.2. Calculating Time Range in Hours, Minutes, and Seconds

Additionally, we can measure our time range just in hours, or just in minutes, or just in seconds whatever we like. To accomplish this, take the steps outlined below.

Steps:

• For calculating the time range just in hours, first select cell E5 and type down the formula as below.

Multiplication with 24 gives the output in hours in Number format.

• Similarly, we can get the time range in minutes or in seconds entirely.

For minutes, the formula is

For seconds, the formula is

Select cells F5 and G5 and set down the above formulas sequentially and press ENTER.

1.3. Using TEXT Function

Using the TEXT function is another quick approach to get the time range without having to change the format. You can specify the format right within the formula using the TEXT function. Follow the steps stated below.

Steps:

• Select cell E5 and put down the formula as follow.

Here h:mm is to specify the text format.

Note: As our output is now in text format, it couldn’t be used as a cell reference for another formula.

2. Calculating Time Range When Time Crosses 12:00 AM in Excel

When the times exceed a day border, calculating the time range becomes more difficult. If the start time is 9:00 PM one day and the end time is 8:00 AM the next day, the end time is less than the start time, and the formula we used in our above approaches will produce a negative number, and Excel will display a string of hash characters (########). For this reason, we’ll use IF and MOD functions here.

2.1. Using IF Function

In this example, we’ve changed our dataset a little bit. We altered the Out Time of Frank and Sandra from present-day to the next day. Consequently, it looks like the Out Time is smaller than the In Time. The steps to be followed are as below.

Steps:

• First, select cell E10 and paste the formula below into the Formula Bar.

Formula breakdown: We used a logical test by implementing the IF function here.

If Out Time is greater than In Time, then the output will be the subtraction value of them like method 1. Otherwise, the value is 1-In Time+Out Time. You can calculate the amount of time on the first day by deducting the start time from 1. Then you can add this to the amount of time on the second day, which is the same as the Out Time.

2.2. Applying MOD Function

The MOD function is an excellent alternative to the formula above. Also, it is handier than the previous function. Follow the operations underneath.

Steps:

• First, select cell E10 and paste the formula below into the Formula Bar, and press ENTER.

Here, we’ve to just give the divisor 1 as Excel interprets a whole day as a numerical value 1.

Note: Neither of the preceding IF and MOD functions can handle a time range longer than 24 hours.

Read More: How to Calculate Range for Grouped Data in Excel

3. Calculating Time Range in Excel with Date and Time

To avoid the previous trouble, we simply use the DateTime format. Here the values contain both date and time. Using this format we can easily the time range longer than 24 hours. Engage with the steps stated below.

Steps:

• Firstly, write down the In Time and Out Time as the image attached below. Select cell E5 and type down the formula as follow and press ENTER.

Here, we can see that the Time Range of cell E10 is 32 hours 30 minutes, which is greater than 1 day.

• Just pay some attention to Format Cells. Select the entire column of Time Range and press Ctrl+1 to open the Format Cells Option. Now, select Custom and write down [h]:mm in the Type box to get the time range over 24 hours and click OK.

4. Applying Excel VBA Code to Calculate Time Range

Application of VBA code is also one of the interesting procedures to solve this problem. Pay attention to the process deeply.

Steps:

• Right-click on the Sheet name and select View Code.

• Instantly, Microsoft Visual Basic for Applications window opens. Right-click on Sheet8 (VBA) > select Insert > Module.

• It opens a code module, where paste the below code down and click on the Run button or press F5.

'Function for calculating Time Range
Public Function TimeRange(InTime, OutTime)
TimeRange = OutTime - InTime
End Function

Here we are creating a user-defined Public Function named TimeRange which we’ll use in our preferred worksheet. The function consists of just the subtraction of Out Time and In Time.

• Now, select cell E5 and type down the formula as below, and press ENTER.

Look, the result is the same as our previous methods.

Read More: How to Calculate Moving Range in Excel

How to Calculate Time Range in Years, Months and Days in Excel

If we have a dataset of just dates and want to know the time range in years or in months or days, the DATEDIF function is perfect for usage.

We have the birth dates of some individuals.

We wanna calculate their present age. How can we get it? What do you think? Let’s see our thinking matches or not.

Steps:

• First, select cell D5 and paste down the formula as follow and press ENTER.

Here, “y” is the unit argument finding the years between two dates. And, the DATEDIF function calculates the time range in just year/month/day format between two values. Using ampersand (&), we concatenated yr with the formula.

Similarly, you can find the time range in months and days. In that cases, “m” and “d” will be the unit argument respectively.

Things to Remember

• Don’t forget to format the cells as per your preferred output. For example: if you want to show the output in hours, minutes, and seconds, use h:mm:ss from Custom Format Cells.
• For a time range greater than 24 hours, use [h]:mm format.
• If you see (#####) this kind of error in any cell, either you have to expand the cell to show the full entity or the value that comes here is negative.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section.

Related Articles

• How to Calculate Bin Range in Excel
• How to Calculate Percentage Range in Excel
• How to Calculate Interquartile Range in Excel

<< Go Back to Range Formula in Excel | Excel Range | Learn Excel