How to Calculate Time Range in Excel (4 Methods)

Below is an Employee Time Sheet dataset containing the “In Time” and “Out Time” of 10 employees for a certain day.

Method 1 – Calculating Simple Time Range in Excel

1.1. Using Simple Subtraction

Steps:

• Select cell E5 and enter the formula as follows:

• Press ENTER.

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

• Use the Fill Handle tool to complete column E.
• Press Ctrl+1 to open the Custom Format Cells option.
• Select h:mm from Type menu’s drop-down list.
• Click OK.

We can see our time range in hours and minutes format.

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

Steps:

• Select cell E5 and enter the following formula:

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

• We can get the time range in minutes or seconds.

For minutes, the formula is:

For seconds, the formula is:

• Select cells F5 and G5, enter the above formulas sequentially, and press ENTER.

1.3. Using the TEXT Function

Steps:

• Select cell E5 and enter the following formula:

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.

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

2.1. Using the IF Function

Steps:

• Select cell E10 and enter the formula below into the Formula Bar:

Formula breakdown: 

If Out Time is greater than In Time, then the output will be their subtraction value, like in 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 the MOD Function

Steps:

• Select cell E10 and enter the formula below into the Formula Bar:

• Press ENTER.

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

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

Method 3 – Calculating Time Range in Excel with Date and Time

Steps:

• Enter the In Time and Out Time as the image attached below.
• Select cell E5 and enter the following formula:

• Press Enter.

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

• Select the entire column of Time Range and press Ctrl+1 to open the Format Cells Option.
• Select Custom and enter [h]:mm in the Type box to get the time range over 24 hours.
• Click OK.

Method 4 – Applying Excel VBA Code to Calculate Time Range

Steps:

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

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

• It opens a code module.
• Enter the code below and click on Run or press F5.

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

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.

• Select cell E5 and enter the following formula:

• Press ENTER.

Read More: How to Calculate Moving Range in Excel

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

We have a dataset of the birth dates of some individuals. We want to know the time range in years, months, or days. We want to calculate their present age.

Steps:

• Select cell D5 and enter the following formula:

• Press enter.

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

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

Things to Remember

• Remember to format the cells according to 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 the Practice Workbook

You can download the following Excel workbook to practice.

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