HomeApplicationOffice informationHow to calculate time in Excel: Add and subtract time

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.

How 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 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 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 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 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 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 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 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 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

You should read it

May be interested

  • ROUNDUP function in Excel - Usage and examplesPhoto of ROUNDUP function in Excel - Usage and examples
    today, the software tips will guide you how to use the roundup function to round numbers (rounding up) in excel. roundup function structure function syntax: = roundup (number, num_digits). in which: + roundup: is the function name. + number: is the number to be rounded up. + num_digits: is the number of digits after the comma you want to round. for example:
  • Match function in Excel - Usage and illustrative examplesPhoto of Match function in Excel - Usage and illustrative examples
    the match function in excel finds a specified item in a specified range and, returns the relative position of that value in this range. function syntax and usage syntax: = match (lookup_value, lookup_array, [match_type]). in which: - match: is the function name. - lookup_value: is the value to search, be it text or numeric.
  • How to count characters in ExcelPhoto of How to count characters in Excel
    to count characters in excel we need the help of the len function. the len function helps you count the number of characters including spaces in a given phrase. normally, the len function is not used alone, it will combine with other functions based on certain purposes.
  • What is a CSV file? Differences between CSV and Excel filesPhoto of What is a CSV file? Differences between CSV and Excel files
    what is a csv file? differences between csv and excel files. if you are an office worker, you've probably met the document saved as a csv. the csv document also makes one of the most popular documents in the world with a small archive capacity.
  • Instructions on how to choose the print area in ExcelPhoto of Instructions on how to choose the print area in Excel
    excel sheets are inherently an interface with data stretching both horizontally and vertically so when printing you will have a few issues worth mentioning. for example, separate print page content or print area limits. today we will talk about one of those issues with tipsmake, which is how to choose the print area in excel.
  • How to print parity pages in ExcelPhoto of How to print parity pages in Excel
    printing parity pages in excel will not be as simple as printing parity in word because it does not have odd pages or even pages functionality. so to make a parity page print in excel, we need to follow a different method. before you can even print odd pages in excel, you need to check the page numbering

Office information