You are currently viewing How to calculate time in Excel

How to calculate time in Excel

  • Post author:
  • Post category:Tips

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;

  1. Time differences
  2. 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 subtract 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))