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.

**Table of Contents**hide

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

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

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

`=(D5-C5)*24`

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

`=(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**.

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

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

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

*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`

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

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

`=TimeRange(C5,D5)`

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.

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.

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.