Buzz
Excel empowers you to perform time calculations effortlessly through addition and subtraction methods, yielding results in minutes, hours, days, months, and years. Explore these fundamental formulas with our software tips.
Calculating Time Using Addition/Subtraction Method
Adding Two or More Time Intervals
Highlight the cell containing the result.
Select the Home tab.
In Format Number, click on the arrow in the bottom right corner.
The Format Cells window appears, choose Custom.
In the Type box, select the option [h]:mm:ss => OK.
=> The result appears after you press OK.
Subtracting time
For example: You have a time range from the start to the end of a task, and your task is to calculate the time needed to complete that task as shown in the table below.
(1) Utilize the formula =End Time – Start Time to calculate the total task duration.
Task B takes one day longer than Task A, yet the displayed results remain identical. This suggests your task isn't quite finished yet. Proceed by following the same steps as when adding two time intervals, as Software Tricks has guided you before.
(2) Highlight the cell containing the result.
(3) Select the Home tab.
(4) In Format Number, click on the arrow in the bottom right corner.
(5) The Format Cells window appears, select Custom.
(6) In the Type box, look for the line [h]:mm:ss.
=> The result will display the number of hours, minutes, and seconds between the two time points.
Calculate time interval using the Datedif function.
Function syntax: =DATEDIF(Start_date,End_date,Unit)
Where:
- DATEDIF is the function name.
- Start_date represents the start date.
- End_date denotes the end date.
- Unit indicates the type of information you want to return.
+ 'Y': represents the completed number of years in the time span.
+ 'M': denotes the completed number of months in the time span.
+ 'D': signifies the completed number of days in the time span.
+ 'MD': the difference between the days from the start date to the end date, disregarding month and year differences.
+ 'YM': the difference between the months from the start date to the end date, disregarding day and year differences.
+ 'YD': the difference between the days from the start date to the end date, disregarding year differences.
You can refer to the following example to understand the formula better.
Above, the software trick has guided you on how to calculate time intervals, add/subtract time in the simplest manner. Depending on the characteristics of your data and reports, you can choose the appropriate calculation method.
Wishing you all success!
