Time is stored in Excel inform of numerical values, and therefore we can perform some mathematical operations on it. Some of the operations that can be performed on time values on Excel are;
- Time differences
- Add/ subtracting times
Let dive deeper into the methods to calculate the mentioned operations.
Calculating Time difference in Excel
The time difference means the time between the time-in and time-out. you can comfortably calculate this using various Excel formulas.
Calculating time difference in hours
To calculate the difference between two times in hours, these formulas are used;
1. To begin with, open your Excel application.
2. Then, open an existing or a new document. If you're working on an existing document, you can open it from your Pc or use the keyboard shortcut to access it (Ctrl + O).
3. If you're working on a new document, enter the dataset of time to be calculated.
4. To change the time format, follow these steps;
- Highlight all the datasets that contain the time values.
- Then, right-click to open a side-view menu.
- On the side-view menu, click on the "Format cells" button.
- On the dialogue box, click on the " Number" Bar, and then, navigate to custom and select the time you want.
- Finally, click the "Ok" button.
5. Click on the first cell, within the Time difference column, and type the address of Time Out minus Time In. For example, =(B2-A2)*24
6. Hit the Enter button and you will now have the time difference in hours. Drag the formula downwards to apply to other cells.
Calculating time difference in minutes
Steps to be followed when calculating time difference in minutes are;
1. To begin with, open an existing or a new document. If you're working on a new document, enter the dataset of time to be calculated.
2. On the first cell within the time difference column, type these commands; =(B2-A2)*24*60
3.
Then, drag the formula downwards to apply to other cells.
4. Note: in case of seconds, this formula should be used =(B2-A2)*24*60*60
Add/ subtracting times
It is possible to either add or substract some arithmetic values from a given time. Two main methods used to achieve this are;
Using Excel Time Function
Time function takes hour value, minute value, and second value and converts them into time format. The converted values can then be used for other operations.
Steps;
1. To begin with, open an existing or a new document. If you're working on a new document, enter the dataset of time to be calculated.
2. To add time to already entered time use this formula, =Time 1+ Time(h,m,s).
h-hour
m-minute
s- seconds
Then hit the Enter button and drag the formula downwards.
The same applies when subtracting time using the Time function. That is, =Time 1+-Time(h,m,s).
Using Basic Arithmetic
Steps;
1. To begin with, open an existing or a new document. If you're working on a new document, enter the dataset of time to be calculated.
2. To add time using basic arithmetic use these formulas;
Hours – =time 1+ (hours to add/24)
Minutes – =time 1+ (minutes to add /(24*60))
Seconds- =time 1+ (minutes to add /(24*60*60))
