How to calculate time in Excel: Add and subtract time

Excel can calculate the amount of time by adding / subtracting method to give you the result of the amount of time in minutes, hours, days, and months. The following software tips will guide you the most basic formulas. The problem is that you have time to complete two steps of work, and you want to know how many hours and minutes it will take to complete the job.

Excel can calculate the amount of time by adding / subtracting method to give you the result of the amount of time in minutes, hours, days, and months. The following software tips will guide you the most basic formulas.

How to calculate time in Excel: Add and subtract time Picture 1How to calculate time in Excel: Add and subtract time Picture 1

Calculate hours by addition / subtraction method

Add two or more time periods

The problem is that you have time to complete two steps of work, and you want to know how many hours it will take, how many minutes to complete the whole job. So you will do the following:

For example: You have task A and task B with the time to complete each step of work as shown in the table below.

(1) Use the Sum function to add two periods.

You have total task time.

How to calculate time in Excel: Add and subtract time Picture 2How to calculate time in Excel: Add and subtract time Picture 2

(2) Highlight the result cell.

(3) Select the Home tab.

(4) In Format Number, click the arrow in the lower right corner.

How to calculate time in Excel: Add and subtract time Picture 3How to calculate time in Excel: Add and subtract time Picture 3

(5) The Format Cells windowappears, select Custom .

(6) In the Type box, select the line [h]: mm: ss => OK.

=> The results appear after you click OK .

How to calculate time in Excel: Add and subtract time Picture 4How to calculate time in Excel: Add and subtract time Picture 4

Subtract time

For example: You have a timeline at the beginning and end of a job, and now your task is to calculate the time it takes to finish that job, as shown in the following table.

How to calculate time in Excel: Add and subtract time Picture 5How to calculate time in Excel: Add and subtract time Picture 5

(1) Use calculation = End time - Start time to calculate the total task time.

How to calculate time in Excel: Add and subtract time Picture 6How to calculate time in Excel: Add and subtract time Picture 6

Mission B takes 1 day longer than task A, however, the results are now the same. That means your mission is not over yet. The next step you follow is the same as the case of adding two periods of time that the Software Tips showed you above.

(2) Highlight the result cell.

(3) Select the Home tab .

(4) In Format Number , click the arrow in the lower right corner.

How to calculate time in Excel: Add and subtract time Picture 7How to calculate time in Excel: Add and subtract time Picture 7

(5) The Format Cells windowappears, select Custom .

(6) In the Type box, find the line [h]: mm: ss .

=> The results will show the number of hours, minutes and seconds between the two time points.

How to calculate time in Excel: Add and subtract time Picture 8How to calculate time in Excel: Add and subtract time Picture 8

Calculate the time distance with the Datedif function

Function syntax: = DATEDIF (Start_date, End_date, Unit)

Inside:

  1. DATEDIF is the name of the function.
  2. Start_date is the start date.
  3. End_date is the end date.
  4. Unit is the type of information you want to return.

+ "Y": is the number of years completed in the period.

+ "M": the number of months completed in the period.

+ "D": is the number of days completed in the period.

+ "MD": the difference between dates starting and ending, ignoring the difference of months and years.

+ "YM": the difference between months from start and end date, ignoring the difference of day and year of day.

+ "YD": the difference between dates starting and ending, ignoring the difference in years.

You can refer to the following example to better understand the above formula.

How to calculate time in Excel: Add and subtract time Picture 9How to calculate time in Excel: Add and subtract time Picture 9

Above software tips have shown you how to calculate the time, add / subtract the easiest time. Depending on the characteristics of the data and the report, you can choose the appropriate calculation.

Good luck!

5 ★ | 1 Vote