How do You Make Time Add Up?
Do you use Excel to keep track of your time? Most VAs I know use some sort of time tracking software which notes what they do. It can be a professional online piece of software which does all kinds of things from tracking your time to monitoring which websites are distracting you!
Some VAs prefer a different approach. Simply keeping a timer on their desk – starting it up when they begin a task and stopping it when they are done. Then they can record the time spent in a notebook or an Excel Spreadsheet.
I get asked a lot – how do I 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 – 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.
- Place the cursor in the cell where you want the answer to appear
- Click on the AutoSum icon and select Sum
- Now drag over the cells to include in the formula
- 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 45 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. So the formula reads =D2-C2 where D2 holds the End Time and C2 the Start Time. Next you can use Autofill to copy the formula down the column so that it is replicated in every row. You will now see the Time Spent total for each row.
Create a Total for All Time Spent
The next step is to create a total for the Time Spent. I did this in another column completely and included in the Sum all the possible rows in the calculation.
This calculation is =SUM(E2:E15) meaning that it will add up any numbers within the cells E2 to E15. If I were to add more rows in, then I will need to update the formula.
You can read about Autofill in Excel –VAs- Why Autofill in Excel Will Save You Hours
You can read more about Sums in Excel – VAs- Why Everything you know about Sums is Wrong
You can learn more about making things add up in my course Essential Excel Skills for VAs, there is both a Windows and a Mac version of the course and you can find them at https://courses.tomorrowsva.com and as a bonus for being one of my readers you get 20% off the list price of the course! Just add the Coupon Code BLOG20 at the checkout.