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

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

Select the cell range B5:C7.

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

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

Select the cells for Difference in Hours to change their format.

From the Format Cells box, select the Custom Category and choose [h] as the format type.
Press OK.

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

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

Fix the format of the cells (B5:C7) by following the procedure from Method 1.
In cell D5, use the following formula and hit Enter.

=TEXT(C5-B5,"h")

Use the Fill Handle to copy the formula to the other cells.

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

Method 3 – Count Hours, Minutes, and Seconds Using a Simple Formula

Fix the formatting of the cell range B5:C7 (follow steps in Method 1).
Insert the following formula in cell D5 and press Enter.

=(C5-B5)*24

Use the Fill handle to copy the formula in the cells below.

In this formula, we multiplied the difference C5-B5 by 24 to get the hours difference.

Use the formula given below in cell E5 and hit Enter.

=(C5-B5)*1440

Copy the formula to the other cells using the Fill Handle.

Here, we multiplied the difference C5-B5 by 1440 to get the minute difference.

In cell F5, use the formula given below and press Enter.

=(C5-B5)*86400

Copy the formula to the cells below using the Fill Handle.

We calculated the seconds by multiplying the difference C5-B5 with 86400.

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

Fix the formats for the cells (B5:C7) by following procedures from Method 1.
Insert the following formula in cell C5 and hit Enter.

=NOW()-B5

Use AutoFill to see the result below.

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

Method 5 – Display Time Difference in the Days, Hours, Minutes, and Seconds Format

Following the procedures from Method 1, fix the cell formatting for the range B5:C7.
Use 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"

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.

Use the Fill Handle to copy the formula to the entire column.

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

Fix the cell formatting for B5:C7 like in Method 1.
Use the following formula in cell D5 and press Enter.

=IF(C5-B5>0, C5-B5, "-" & TEXT(ABS(C5-B5),"h"))

Use the Fill Handle to copy the formula to the cells below.

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.

Download the Practice Workbook

Calculate Hours from Date and Time.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!