Time Calculation in Excel (Time Difference, Time Addition and Subtraction)

In this Lesson, you will learn how to calculate times in Excel in different techniques as mentioned below:

1. How to Type Times in Excel.
2. How to Calculate Total Hours Worked or Difference Between Two Time (Time-In & Time-Out) in Excel.
3. Adding and Subtracting Time in Excel.

Technique # 1: How to Type Times in Excel.

In Microsoft Excel, If you want to enter time in worksheet then you must keep in mind that, you will enter Colon (:) between hours and minutes instead of full-stop (.)

In Excel, you have two option, one is 24 hour system and another is am/pm system.

♥ To use the am/pm system, always require to Enter am or pm after the time and don't forget to leave space between the number and the text as mentioned below:

♥ If you would like to change your time into 24 hour system. Then please follow below mentioned steps:

Step 1: First Select your time as mentioned below.

Step 2: Now in Number group, Click on Number format button to open the format Cells window.

Step 3: In Format Cells window, First Click on Time and Select the 13:30 format and Press OK to save the setting as mentioned below:

Now you can see the result as mentioned below:

Technique # 2: Calculate Difference Between Two Time (Time-In & Time-Out) in Excel.

In Microsoft Excel, you can easily calculate the difference between two time or elapsed time.

For example: Find Actual Hours worked with the help of Time-In and Time-Out.

If you are working as Payroll Manager and you wanted to analyse the Actual Hours Worked of your employees on the basis of Time-In and Time-Out data, which you have collected from attendance machine, then you can use this technique.

By using below mentioned steps, you can easily calculate difference between two time:

Step 1: First create Employees Detailed Time Sheet which contains Time-In and Time-out as mentioned below.

Step 2: Now Select the Cell F6 and Type below mentioned formula.

=INT(E6-D6) & " days, " & HOUR(E6-D6) & " hours, " & MINUTE(E6-D6) & " minutes and " & SECOND(E6-D6) & " seconds"

Now you can see the result as mentioned below:

Step 3: Now Copy this Cell F6 and paste this copied cell to all empty cell as mentioned below:

You can also use this alternate way to calculate difference with the help of subtraction (E6-D6) and below mentioned custom format:

d "days," h "hours," m "minutes and" s "seconds"

To implement this above mentioned format, please follow below mentioned steps:

Step 1: First Select F6 Cell and type =(E6-D6) Formula for subtraction as mentioned below.

Step 2: Now Click on Number Format Button to Open Format Cells Window.

Step 3: Now in Format Cell window, Select category Custom, Paste the above Format in Type section and Click OK to save the setting.

Now you can see the result as mentioned below:

Technique # 3: Adding and Subtracting Time in Excel.

In Microsoft Excel, you can easily add or subtract hour in so many ways some of these are mentioned below:

1. Add or Subtract Time Under 24 hours.
2. Add or Subtract Time Under or Over 24 hours.
3. Add or Subtract Time Under 60 minutes.
4. Add or Subtract Time over 60 minutes.

1: Add or Subtract Time Under 24 hours.

Example # 1: If your employee has gone to his home after work done of two hours and you don't know the Time-Out of your employee, because your employee had forgot to Card-Out at the time of leaving his office.

For Calculation of Time-Out under 24 hours, you can use below mentioned formula:

=First Time (Time-In) + TIME(N hours, 0, 0)

Now you can use this formula as mentioned below:

Example # 2: If you don't know the Time-In of your employee, but you have the Time-Out of your employee. So you need to subtract Two hours to get Time-In as per the above mentioned example.

For Subtraction, you can use this below mentioned formula:

=End Time (Time Out) - TIME(N hours, 0, 0)

Now you can use this formula as mentioned below:

2: Add or Subtract Time Under or Over 24 hours.

In Microsoft Excel, if you want to Add or Subtract more than 24 hours, then you can use these formulas as mentioned below:

Formula For Addition: 

=Time-In (Start Time)+(N hours / 24)

Formula For Subtraction:

=Time-Out (End Time)-(N hours / 24)

For instance, we will Add or Subtract 30 hours with the help of above mentioned formula. Result mentioned below:

Addition:

Subtraction:

3: Add or Subtract Time Under 60 minutes.

In Microsoft Excel, If you want to Add or Subtract time under 60 minutes, then you can use these formulas as mentioned below:

Formula for Addition: 

=Time-In (Start Time)+TIME(0, N minutes, 0)

Formula for Subtraction:

=Time-Out (End Time)-TIME(0, N minutes, 0)

For Instance, we will Add or Subtract 30 minutes with the help of above mentioned formula. Result mentioned below:

Addition:

Subtraction:

4: Add or Subtract Time over 60 minutes.

In Microsoft Excel, If you want to Add or Subtract time over 60 minutes, then you can use these formulas as mentioned below:

Formula for Addition: 

=Time-In (Start Time)+(N minutes / 1440)

Formula for Subtraction:

=Time-Out (End Time)-(N minutes / 1440)

For Instance, we will Add or Subtract 120 minutes (2 hours) with the help of above mentioned formula. Result mentioned below:

Addition:

Subtraction:

---Thank You---