Microsoft Excel - How do you make time add up?

Do you use Excel to keep track of your time? It may be time spent on a project or a specific task. I get asked how to make time add up a lot.

How do you make time add up?

First of all, you need to make sure that Excel knows you are adding time to the worksheet and not just random numbers or data. To do this select the cells where you will be adding in the time values and format them as Time.

On the Home Ribbon
Click the drop down in the Number Formats box
Choose Time

Now you won’t actually see anything on the worksheet, the cells will look like normal. It is only when you start to add numbers in that you will see the Time Format and then you need to type the numbers in correctly for the Time Format to work.

To see numbers as time, they need to follow the number format and you will need to type them in like this:

02:05:00

02 – hours

05 – minutes

00 – seconds

If you simply type in 2 then you will see 00:00:00 as Excel does not know whether the 2 is hours, minutes or seconds!

Next do a sum

Now that you have added the data to the spreadsheet and it is formatted correctly Excel will add it up using the Sum Function as normal.

1. Place the cursor in the cell you want the answer to appear

2. Click on the AutoSum icon and select Sum

3. Now drag over the cells to include in the formula

4. Press Enter

You will see that Excel has added up the column of times and you have the correct answer. In this case 1 hour and 55 minutes.

What about working out the difference between the start and end times?

In this case, you will need to have a column for the Start Time and a column for the End Time. You can then enter the values as times and calculate End Time minus Start Time.

To calculate use the Cell References and the minus sign.

The formula reads – = I2-H2 and gives me the total time spent on each task.

Create a total for all time spent

The next step is to create a total for the Time Spent I simply used the Autosum function to add up the column.

To learn more about Microsoft Excel and how you can speed up routine tasks head over to Essential Excel for The Savvy Assistant which is just one of the courses that make up Essential Office for The Savvy Assistant.

Categories: Blog, Microsoft Excel