At some point you will need to work out the difference between two times or sum up a list of times. Adding up hours and minutes separately is time consuming even with a calculator, so Excel is a natural choice for this job, once you know how.
Calculating times in Excel is relatively easy, and useful – you can use this technique on a timetable or timesheet to calculate time between two times, and then use a formula to tally up total hours. Although you can use simple formulas to do this, how you format the cells is vital.
.png)
Excel stores times as decimal fractions and then displays those numbers in a format we understand – for example, if you enter the number 0.333333 in cell and formatted as a time, from the Number Format list on the home tab, it will display as 08:00am.
.png)
To check if the times in your Excel sheet can be used in a formula, select the cell and press the CTRL, Shift and 1 keys together. If the time changes into a number, then you’re good to go. Press the CTRL and Z keys to return it to its original format. Dates can be calculated using simple formulas (see July 2015 issue)
.png)
If you have a start time and a duration time in two different cells, a simple formula like =A1+B1 where A1 contains the start time, and B1 the duration, will calculate the finish time. When you add time, Excel works as a clock, starting from 0 each time 24 hours is tallied up, so a formula adding up three cells with 10:00 in them will display 6:00, not 30:00.
.png)
If you want to add up a list of times (or durations) then the cell that contains the formula has to be formatted differently. Select the cell and press the CTRL and 1 keys together. Select the Number tab, and then select the “Custom” category. In the list of options, find the [h]:mm:ss option and apply that format.
