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.
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.
(2) Highlight the result cell.
(3) Select the Home tab.
(4) In Format Number, click the arrow in the lower right corner.
(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 .
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.
(1) Use calculation = End time - Start time to calculate the total task time.
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.
(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.
Calculate the time distance with the Datedif function
Function syntax: = DATEDIF (Start_date, End_date, Unit)
Inside:
- DATEDIF is the name of the function.
- Start_date is the start date.
- End_date is the end date.
- 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.
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!