How to Calculate Hours from Date and Time in Excel (6 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to calculate the hour between 2 times from different or the same date. It’s quite simple to do in Excel. We can find the hour difference in several ways. In this article, we will discuss 6 such easy and simple methods to calculate hours from date and time in Excel.


How to Calculate Hours from Date and Time in Excel: 6 Easy Methods

We can calculate hours from date and time in Excel following different methods. Here we will show you 6 simple and easy methods to calculate hours from date and time in Excel. In our dataset, we can see the starting time (B5:B7) and the end time (C5:C7) of a small project. Now, we will calculate the hours.


Method 1: Subtract One Time from Another to Calculate Hours in Excel

In this method, we will directly subtract one time from another to calculate the hour difference. We are discussing the steps below for this method.

  • In the dataset, we select the date and time first to change the format.
  • First, select the cell range B5:C7.

 how to calculate hours from date and time in excel

  • Go to Number Format of the Home tab in the ribbon and select More Number Formats.

  • From the Format cell box, we select the Custom format and choose dd-mm-yy h:mm as the format type.
  • Then press OK.

  • Now, we select the cells for Difference in Hours to change its format.

Subtracting one time from another

  • From the Format Cells box, we select the Custom Category and choose [h] as the format type.
  • After that, press OK.

  • Next, we write the following formula in cell D5 and hit Enter.
=C5-B5
  • Use the Fill Handle to copy the formula in the cells below.

In the formula, C5-B5 denotes the difference in time between the two cells in hours.
  • We have calculated the hour difference between two different times successfully.

Read More: Excel Calculate Hours Between Two Times After Midnight


Method 2: Get Hours from Date and Time with Excel TEXT Function

We can also use The TEXT Function of Excel to get the hour difference between two dates and times. We need to fix the format of the cells (B5:C7) by following the procedure from  Method 1.

  • First, in cell D5 we write the following formula and hit Enter.
=TEXT(C5-B5,"h")
  • Next, use the Fill Handle to copy the formula in the below cells.

Get Hours from Date and Time with Excel TEXT Function

Here, the TEXT function displays the difference C5-B5 in “h” (Hour) format.

Method 3: Count Hours, Minutes, Seconds Using Simple Formula

We can also calculate the difference between two dates and times in hours, minutes, and seconds by using a very simple formula. Following procedures from  Method 1 we need to fix the formats of the cell range B5:C7 first. Then we follow the following steps.

  • In the beginning, we write the following formula in cell D5 and press Enter.
=(C5-B5)*24
  • Now, use the Fill handle to copy the formula in the following cells.

Count Hours, Minutes, Seconds Using Simple Formula

In this formula, we multiplied the difference C5-B5 by 24 to get the hours difference.
  • Then we write the formula given below in cell E5 and hit Enter.
=(C5-B5)*1440
  • We copy the formula in the below cells using the Fill Handle.

Here, we multiplied the difference C5-B5 by 1440 to get the minute difference.
  • After that, in cell F5 we write the formula given below and press Enter.
=(C5-B5)*86400
  • Now, we copy the formula in the cells below using the Fill Handle.

We calculated the seconds by multiplying the difference C5-B5 with 86400.
  • Yahoo! We have successfully calculated the difference in time in hours, minutes, and seconds.

Method 4: Calculate Elapsed Hours in Excel from Start Time to Present

We can calculate the elapsed time by subtracting the starting time from the present time. Excel has the NOW function to determine the present date and time, we will use that in this method. We fix the formats for the cells (B5:C7) by following procedures from Method 1. We are showing the steps for this method below.

  • Firstly, write the following formula in cell C5 and hit Enter.
=NOW()-B5
  • After that, use AutoFill to see the result below.

Calculate Elapsed Hours in Excel from Start Time to Present

The NOW function gives the present time from where we subtracted the time from cell B5.

Read More: Calculate Elapsed Time Between Two Dates in Excel


Method 5: Display Time Difference as Days, Hours, Minutes, Seconds Format

In this method, we will calculate the difference as days, months, minutes, and seconds. Following procedures from Method 1, we fix the cell (B5:C7) format first. Now we follow the steps given below.

  • Write the following formula in cell D5 and hit Enter.
=INT(C5-B5) & " days, " & HOUR(C5-B5) & " hours, " & MINUTE(C5-B5) & " minutes and " & SECOND(C5-B5) & " seconds"

Display Time Difference as Days, Hours, Minutes, Seconds Format

In the formula, the INT function calculates the integer number of difference C5-B5 which gives the days. The HOUR function, the MINUTE function & the SECOND function do similar tasks in the formula and return in hour, minute & second format respectively.
  • Further, we use the Fill Handle to copy the formula in the following cells.

  • Finally, we have calculated the difference in time in days, hours, minutes, and seconds.

Read More: How to Calculate Hours Between Two Times in Excel


Method 6: Calculate and Display Negative Hours from Date and Time in Excel

So far we calculated the time difference as positive, we may want to subtract the time from any past time which will give a negative difference. We fix the cell (B5:C7) format following procedures from Method 1. We can calculate the negative hours following the steps below.

  • First, write the following formula in cell D5 and press Enter.
=IF(C5-B5>0, C5-B5, "-" & TEXT(ABS(C5-B5),"h"))
  • Then, use the Fill Handle to copy the formula in the cells below.

Calculate and Display Negative Hours from Date and Time in Excel

Here, we used the IF function of Excel. C5-B5>0 is the criteria, and C5-B5 is the result if the criteria match. If the criteria do not match then a negative sign is given before the result. The TEXT function gives the output as an hour. The ABS function gets the absolute value of C5-B5.
  • Hurrah! We have successfully calculated the negative hour difference.

Read More: How to Calculate Travel Time Between Two Cities in Excel


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

Hour difference is very useful in time of different date and time calculations. It’s quite easy in Excel to find the hour difference. In this article, we have shown 6 easy methods to calculate hours from date and time in Excel. If you face any problem following the steps, please let us know by commenting.


Related Articles


<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo