How To Add Time In Excel (Without Wasting It)

People who can easily add and subtract large numbers in their heads still struggle with adding and subtracting units of time.  Converting hours and minutes even within the same calendar day seems nothing short of a trick and/or impossible question.  I don’t know if it has to do with the variable scales of hours, minutes and seconds, or if could be as simple as overthinking the colon, but dealing with time mathematically has never been easy—until, of course, the invention of Microsoft Excel.  Adding and subtracting time in Excel is so easy even technophobes will throw up their hands and rejoice.  So get ready to celebrate, and once you have time down pat, make the most of it with this CFA approved Excel training program for all skill levels (you can even earn 3 CE credit hours—pretty cool).

Time you enjoy wasting is not wasted time.

Having fun yet?  Great.  Let’s get down to business.  It doesn’t really matter why you need to add or subtract time, but let’s assume you’re a contract worker and have been logging hours on a weekly invoice.  Obviously, you don’t want to cheat yourself out of precious hours of hard labor, but you also don’t want to overbill your soon-to-be-vehement clients.  First, let’s have a look-see at how hard you’ve been working:

Working a full day on New Years.  I’m impressed.  To calculate the total number of hours, we will use the AutoSum function.  I’m going to click cell B10, below my list of times.  Now, depending on your version of Excel, you might find the AutoSum function in different places.  In most cases, it will appear beneath the Home tab; otherwise it will be located in the Formula tab, and is designated by the symbol of a Greek Sigma (lower left):

Next, I click AutoSum while selecting cell B10.  You will notice it automatically generates the appropriate range:

All we have to do is press Enter or Tab to get the result:

Even the most time-illiterate mathematician knows that isn’t the correct answer.  What went wrong?  Believe it or not, you worked too much (while you’re at it, workaholics can get the most out of their anxiety by learning how to boost productivity through time management).  The AutoSum function only works if the sum is less than 24 hours, which isn’t a bad thing to know.  For adding smaller values, AutoSum will always be the quickest and easiest method.  Fortunately, calculating sums greater than 24 is an easy fix.

I’m going to go back and select cell B10 again.  I can either format cells using my Home tab or by simply right-clicking B10 and selecting Format Cells:

If formatting cells is new ground for you, it might be time to get a comprehensive grasp on the basics of Excel.

I never thought I’d say this, but newer versions of Excel actually work semi-intuitively at this point.  The Format Cells dialogue box appears and automatically conducts you to the most relevant location: under the Number tab, it will have selected Custom and an appropriate Type:

The problem is, the correct Type does not yet exist.  But no worries.  Next to Type, enter the following format: [h]mm;@.  Note: the period is NOT part of the type.  Now tab over and you’ll have the desired result, a quantification of your life’s work:

You will be glad to hear that you will not have to enter [h]mm;@ every time you want to add times; it will automatically be saved as a Type.  I also want to clarify that it is not necessary to format all the cells in this manner.  Only the cell in which the sum will be generated needs to be formatted.

Subtracting Time

Naturally, this is a very similar process to adding time, but we’ll learn a few new tricks along the way.  First, we need a new data set.  Let’s look at runners’ start and finish times for a marathon, which will include both AM and PM:

We want to subtract the start time from the finish time, so in cell D2, enter the formula =C2-B2:

The number is correct, but the “AM” is nonsensical; elapsed time cannot be expressed this way.  We need to format the cell.  Select cell D2 and open the Format Cells dialogue box.  Just like we did when adding times, under the Number tab go to Custom.  This time, however, Excel already has the Type we need.  If Excel didn’t select it for you, find h:mm and click OK.  This gets you down to the bare essentials: hours and minutes.  Go ahead and drag the fill handle down:

We see that Johnson won handily.  That was relatively easy.  But let’s imagine our runners were actually competing in a decathlon that lasted longer than 24 hours.  Our new data set might look like this:

Notice the absence of AM/PM, as well as the 24-hour clock appearing in the Finish column.  Again, we need to create a new Type.  This time, select the range B2 through C5.  We want to format all the cells with entries.  Open the Format Cells dialogue box and navigate back to Type.  Enter, or modify the existing type to: m/d/yyyy h:mm AM/PM.  As before, the new format will be saved in your Type list.  Now our times are formatted appropriately:

Looking good.  Our final act involves a tiny Type modification.  In the first cell below Elapsed, I’m going to enter: =C2-B2, drag the fill handle down, and generate the following results:

Clearly, these times are incorrect.  We already know they should be in excess of 24 hours.  Go to Type, and modify the existing type (which should be h:mm) by inserting brackets around the “h”: [h]:mm.  This time, we get the cold, hard truth: Pryor just managed to oust Johnson:

How did it get so late so soon?

Excellent question, Dr. Seuss.  In the off chance you’re speaking literally, we are now well prepared to answer your question.  Well, if I’ve said it once, I’ve said it a thousand times: Excel may not be intuitive, but it is easy to learn.  Confident beginners can quickly go pro in Excel with this awesome intermediate course.  That’s all, folks!  See you next time.