How to add and subtract time in Excel in an extremely simple way
You will need addition, subtraction of time when making time sheets, calculating working hours, overtime, hotel stays… And when you need to calculate the time between two points in time. Adding and subtracting time is a basic operation, but not everyone knows how to do it quickly and accurately. The following article will guide you through some common addition and subtraction operations and use the DATEDIF function to calculate time. Please follow along.
1. Add two periods of time
With this operation, you need to complete two steps of a task. From there, you will calculate the total time required to complete that task. Please observe the example below to understand more clearly.
In the Excel interface, you need to have a data table like the one below. To calculate the total time, use the SUM function to add the two steps performed.
The user uses the syntax: =SUM(B2:C2) and gets the result as follows:
Next, you need to highlight the cell containing the result, then select the Home tab on the toolbar. Then, under Format Number, click the arrow in the bottom right corner.
Immediately afterwards, the Format Cells window appears on the screen and you select Number. Then, in the Custom section below, find the [h]:mm:ss line under the Type section. Then click OK to complete.
The result will then be displayed as follows:
2. Subtract two periods of time
To subtract two periods of time, you also need a data table like the one above. To calculate the total time, subtract the end time from the start time. The result will then be returned as follows:
Then you also highlight the result and perform the same operations as above to convert the time to [h]:mm:ss format. Observe the image below for clearer instructions:
Then we get the result between the 2 time points as follows:
3. Calculate time intervals using the DATEDIF function
The DATEDIF function is commonly used to calculate the time interval between 2 time points. You use the syntax as follows: =DATEDIF(Start_date,End_date,Unit)
- 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. Specifically:
+ “Y”: the number of complete years in the time period.
+ “M”: the number of complete months in the time period.
+ “D”: the number of complete days in the time period.
By observing the example below, you will understand more clearly about this function. To calculate the number of days, months, or years between the start date and the end date, you just need to apply the syntax as shown in the image below. The result returned will be as follows:
This article has introduced readers to how to add and subtract time in Excel in a very simple way. We hope this article will provide useful information for readers. Good luck with your implementation!