How to Calculate Hours and Minutes in Excel (7 Handy Ways)

In this article, I will show you 7 simple and effective ways to calculate hours and minutes in Excel. In Excel, if you simply enter a number and want to convert that number into time, then you would see that the integer part of the number will represent the date calculated from 01-Jan-19000. Following, the decimal part of the number represents the time with respect to 24 hours. For example, if you write 2.5, it would represent 02-Jan-1900, 12:00 PM. Read this article to discover more.


Download Practice Workbook

You can download our practice workbook form here for free.


7 Easy Ways to Calculate Hours and Minutes in Excel

Say, we have a dataset of an employeeโ€™s entry time and exit time to the office throughout the week. Now, we need to calculate how much time he worked each weekday. You can use any of the 7 easy ways below to calculate hours and minutes in Excel. ๐Ÿ‘‡

Sample Dataset to Calculate Time in Hours and Minutes in Excel


1. Using Subtraction Functionality

You can simply use the subtraction functionality of Excel to calculate time in hours and minutes. Follow the steps below to accomplish this. ๐Ÿ‘‡

Steps:

  • Firstly, click on the E5 cell where you want to calculate your time in hours and minutes. Following, write the formula below and subsequently, press the Enter button.
=D5-C5

Use Subtraction Functionality to Calculate Time in Hours and Minutes in Excel

Note:

D5 and C5 both cells should be in 24 hours time format or h: mm custom format.

  • At this time, you have got the working time of the employee for Monday. Now, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all.

Drag the Fill Handle to Copy Formula

Thus, you can calculate time in hours and minutes for all the weekdays. And, the result will look like this. ๐Ÿ‘‡

Calculated Time in hours and Minutes

Read More: How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)


2. Combine HOUR and MINUTE Functions

You can also use the HOUR and MINUTE functions to calculate time in hours and minutes.

The HOUR function has only one argument which is serial_number. It means it takes a time value and returns only the hours value of that time.

HOUR Function Syntax

The MINUTE function has only one argument which is serial_number. It means it takes a time value and returns only the minutes value of that time.

MINUTE Function Syntax

Follow the steps below to do this. ๐Ÿ‘‡

Steps:

  • Firstly, click on the E5 cell where you want to calculate your time in hours and minutes. Following, write the formula below and subsequently, press the Enter button.
=HOUR(D5-C5)&":"&MINUTE(D5-C5)

Using HOUR and MINUTE Function to Calculate Hours and Minutes in Excel

๐Ÿ”Ž Formula Breakdown:
  • =HOUR(D5-C5)

This subtracts the D5 and C5 cellsโ€™ time and returns only the hour value of the subtraction.

Result: 8

  • =HOUR(D5-C5)&โ€:โ€

This concatenates a colon after the previous result.

Result: 8:

  • =HOUR(D5-C5)&โ€:โ€&MINUTE(D5-C5)

This subtracts the D5 and C5 cellsโ€™ time and returns the minute value of the subtraction and concatenates it with the precious result.

Result: 8:35

  • At this time, you have got the working time of the employee for Monday. Now, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all.

Drag Fill Handle to Copy Formula

Thus, you can calculate time in hours and minutes for all the weekdays. And, the result will look like this. ๐Ÿ‘‡

Calculated Time in Hours and Minutes

Read More: How to Subtract Time in Excel (7 Quick Methods)


3. Use Excel TIME Function to Calculate Hours and Minutes

Besides, you can use the TIME function to calculate time in hours and minutes in Excel.

The TIME functionย has mainly 3 arguments. Such as hour, minute, and second. The hour argument takes the hour value of a time. Similarly, the minute and second arguments take the minute and second values respectively.

TIME Function Syntax

Follow the steps below to accomplish this. ๐Ÿ‘‡

Steps:

  • Firstly, click on the E5 cell where you want to calculate your time in hours and minutes. Following, write the formula below and subsequently, press the Enter button.
=TIME(HOUR(D5),MINUTE(D5),SECOND(D5))-TIME(HOUR(C5),MINUTE(C5),SECOND(C5))

๐Ÿ”Ž Formula Breakdown:
  • =TIME(HOUR(D5),MINUTE(D5),SECOND(D5))

This extracts the hour, minute, and second value from the D5 cellโ€™s time and writes in the organized time value.

Result: 17:55

  • =TIME(HOUR(C5),MINUTE(C5),SECOND(C5))

This extracts the hour, minute, and second value from the C5 cellโ€™s time and writes in the organized time value.

Result: 9:20

  • =TIME(HOUR(D5),MINUTE(D5),SECOND(D5))-TIME(HOUR(C5),MINUTE(C5),SECOND(C5))

This subtracts the second time value from the first time value.

Result: 8:35

  • At this time, you have got the working time of the employee for Monday. Now, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all.

Drag the Fill Handle to Copy Formula

Thus, you can calculate time in hours and minutes for all the weekdays. And, the result will look like this. ๐Ÿ‘‡

Calcuated Time in Hours and Minutes

Read More: How to Calculate the Duration of Time in Excel (7 Methods)


Similar Readings


4. Utilize Excel TEXT Function

You can also use the TEXT function to calculate time in hours and minutes.

The TEXT function has mainly 2 arguments. Such as value and format_text. The value argument takes the value you want to show. And, the format_text argument takes the format in which you want to show your value.

TEXT Function Syntax

Follow the steps below to do this. ๐Ÿ‘‡

Steps:

  • Firstly, click on the E5 cell where you want to calculate your time in hours and minutes. Following, write the formula below and subsequently, press the Enter button.
=TEXT(D5-C5,"h:mm")

Using TEXT Function to Calculate Hours and Minutes in Excel

  • At this time, you have got the working time of the employee for Monday. Now, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all.

Drag the Fill Handle to Copy the Formula

Thus, you can calculate time in hours and minutes for all the weekdays. And, the result will look like this. ๐Ÿ‘‡

Calculated Time in Hours and Minutes

Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)


5. Use Excel NOW Function to Calculate Elapsed Time in Hours and Minutes

Moreover, you can calculate time in hours and minutes at the current time for a given time using the NOW function.

The NOW functionย has no arguments. It only returns the current time.

NOW Function Syntax

Follow the steps below to do this. ๐Ÿ‘‡

Steps:

  • Firstly, click on the D5 cell where you want to calculate your time in hours and minutes. Following, write the formula below and subsequently, press the Enter button.
=NOW()-C5

Using NOW Function to Calculate Elapsed Time in Hours and Minutes

  • At this time, you have got the working time of the employee for Monday. Now, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all.

Drag the Fill Handle to Copy Formula

Thus, you can calculate time in hours and minutes for all the weekdays. And, the result will look like this. ๐Ÿ‘‡

Calculated Time in Hours and Minutes

Read More: How to Calculate Elapsed Time in Excel (8 Ways)


6. Apply Excel MOD Function to Calculate Time in Hours and Minutes

Besides, you can use the MOD function to calculate time in hours and minutes.

The MOD function has two arguments mainly. Such as number and divisor. The number argument requires the number that is to divide. In the second argument, the divisor requires the number by which you want to divide. MOD function returns the remainder after the division.

MOD Function Syntax

Follow the steps below to accomplish this. ๐Ÿ‘‡

Steps:

  • Firstly, click on the E5 cell where you want to calculate your time in hours and minutes. Following, write the formula below and subsequently, press the Enter button.
=MOD(D5-C5,1)

Using MOD Function to Calculate Hours and Minutes

  • At this time, you have got the working time of the employee for Monday. Now, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all.

Drag Fill Handle to Copy Formula

Thus, you can calculate time in hours and minutes for all the weekdays. And, the result will look like this. ๐Ÿ‘‡

Calculated Time in Hours and Minutes

Read More: How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)


7. Combine IF, HOUR and MINUTE Functions

Moreover, you can use the IF function to calculate time in hours and minutes. It is handy in case of negative time results and getting errors.

The IF function has mainly 3 arguments. Such as logical test, value_if_true and value_if_false. In the first argument, the logical test requires the condition to test if it is true or false. The second argument, value_if_true requires the value which will be returned if the logical test is true. The third argument, value_if_false requires the value which will be returned if the logical test is false.

IF Function Syntax

You can follow the steps below to accomplish this. ๐Ÿ‘‡

Steps:

  • Firstly, click on the E5 cell where you want to calculate your time in hours and minutes. Following, write the formula below and subsequently, press the Enter button.
=IF(HOUR(D5-C5)>0, HOUR(D5-C5) & " hours, ","") & IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & " minutes","")

Using IF Function to Calculate Hours and Minutes in Excel

๐Ÿ”Ž Formula Breakdown:
  • =IF(HOUR(D5-C5)>0, HOUR(D5-C5) & โ€ hours, โ€œ,โ€โ€)

This checks if the hour difference of D5 and C5 cellsโ€™ time is positive. If positive it returns the hour difference. If negative it would return a blank. Following, it would concatenate theย  โ€œhours, โ€ text.

Result: 8 hours,

  • =IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & โ€ minutesโ€,โ€โ€)

This checks if the minute difference of D5 and C5 cellsโ€™ time is positive. If positive it returns the minute difference. If negative it would return a blank. Following, it would concatenate the โ€œminutes, โ€ text.

Result: 35 minutes

  • =IF(HOUR(D5-C5)>0, HOUR(D5-C5) & โ€ hours, โ€œ,โ€โ€) & IF(MINUTE(D5-C5)>0, MINUTE(D5-C5) & โ€ minutesโ€,โ€โ€)

It would concatenate the two previous results.

Result: 8 hours, 35 minutes

  • At this time, you have got the working time of the employee for Monday. Now, place your cursor in the bottom right position of your cell.
  • Following, the fill handle will appear. Subsequently, drag the fill handle below to copy the formula below for all.

Drag the Fill Handle to Copy the Formula

Thus, you can calculate time in hours and minutes in Excel for all the weekdays. And, the result will look like this. ๐Ÿ‘‡

Calculated Time in Hours and Minutes in Excel

Read More: How to Subtract and Display Negative Time in Excel (3 Methods)


Things to Remember

  • Formatting is very important when calculating time in Excel. It is better to set the format in 24-hour format from the format window. Else, you can choose a custom format as h::mm too.
  • Sometimes, it might show that the result returned is a #### error like this. This happens for two reasons, such as Narrow Columns and Negative Results. For narrow columns case, expand the column. For the negative results case, use the IF function to apply different statements or formulas when negative results appear.

Conclusion

So, in this article, I have shown you 7 effective ways to calculate time in hours and minutes in Excel. Go through the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

2 Comments
  1. I don’t need the start and end times, I am just looking to add time values. On Monday, I worked 8h 28m min driving and 4h 59m not driving, on Tuesday, 9h 11m driving and 3h 2m not driving, ect… how do i set up a spread sheet where put those hours in and it auto sums? Thanks

    • Hi, Bill!
      Thank you for your query.
      Regarding your query, just make a dataset table and use the SUM function or the addition functionality of Excel. In our article, we have used the subtraction functionality; in your requirements, you just need to utilize the addition functionality of Excel.

      Regards,
      Tanjim Reza

Leave a reply

ExcelDemy
Logo