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.


Download Practice Workbook

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


4 Methods to Calculate 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.

How to Calculate Time Range in Excel

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


1. Simple Time Range Calculation

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. Adopting 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.
=D5-C5

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

How to Calculate Time Range in Excel Using subtraction

  • 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.

How to Calculate Time Range in Excel Format Cells Option

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

How to Calculate Time Range in Excel


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.
=(D5-C5)*24

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

How to Calculate Time Range in Hours, Minutes, Seconds in Excel

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

For minutes, the formula is

=(D5-C5)*24*60

For seconds, the formula is

=(D5-C5)*24*60*60

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

How to Calculate Time Range in Excel with Formula


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.
=TEXT(D5-C5,"h:mm")

Here h:mm is to specify the text format.

How to Calculate Time Range in Excel Using TEXT Function

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


2. When Time Crosses 12:00 AM

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.
=IF(D10>C10,D10-C10,1-C10+D10)

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.

How to Calculate Time Range in Excel Using IF Function


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.
=MOD(D10-C10,1)

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

How to Calculate Time Range in Excel Using MOD Function

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


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.
=D10-C10

How to Calculate Time Range in Excel with Date and Time

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.

How to Calculate Time Range in Excel Format Cells Option


4. Applying VBA Code

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.

How to Calculate Time Range in Excel Applying VBA Code

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

Applying VBA Code

  • 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.

Applying VBA Code

  • Now, select cell E5 and type down the formula as below, and press ENTER.
=TimeRange(C5,D5)

Applying VBA Code

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


How to Calculate Time Range in Years, Months and Days

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.

Using DATEDIF Function

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.
=DATEDIF(C5,TODAY(),"y")&" yr"

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.

Using DATEDIF Function

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.

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. Please visit our website Exceldemy to explore more.

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo