How to Add Hours and Minutes in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Turn AM/PM labels on or off in the Format Cells window.
Image Credit: Marcus Miranda/iStock/Getty Images

Using Microsoft Excel you can add hours and minutes together in two different ways: by using the TIME function to combine time data from multiple cells or by using basic addition on cells formatted as times. The first method works best when you have existing columns of hours and minutes you need merged, while the second method allows you to add the durations of multiple separate events or figure out the final ending time of several consecutive events.

Advertisement

Adding Separated Hours and Minutes

Video of the Day

To combine hours and minutes into a single cell, use the TIME function, which counts time starting at midnight. For example, if you have "10" in cell A1 and "30" in cell B1, type "=TIME(A1,B1,0)" (without quotes here and throughout) to receive the combined time "10:30 AM." The "0" in the TIME function corresponds to seconds -- enter a third variable or just use "0" to ignore the seconds. To add multiple hour or minute cells together, list all the cells in one part of the function, such as "=TIME(A1,B1+B2+B3,0)" to combine the minutes from the first three cells in B. Using the TIME function does not require any manual formatting changes, but you might want to use custom formatting on your source data for legibility. For example, select the column with your hour data, right-click it and pick "Format Cells." Choose "Custom" and type "General "hours"" (again, without the external quotes) to make the cells display the word "hours" without affecting math operations.

Advertisement

Video of the Day

Combining Clock Times

Excel can add two clock times using the regular addition function as long as all cells are formatted as times. If you type a clock time such as "5:30," Excel automatically changes the cell to the correct format. When working with existing data or if you have times over 24 hours, you might need to set the format manually: right-click the cell, pick "Format Cells," choose "Time" and select a style. For times over 24 hours, pick a style that includes an "h" in brackets. Note that styles with an asterisk adjust times when you change your computer's time zone. To add two times, just use the plus sign -- if A1 contains "5:30" and A2 contains "1:30", enter the formula "=A1+A2" to get the result "7:00." Clock times in calculations don't have to actually exist on a normal clock, so you can add "5:30" and "0:30" to get "6:00."

Advertisement

Adding Times With Dates

When adding times with included dates, you might end up with an unexpected result. For example, adding "1/1/2014 5:30" and "1/1/2014 6:00" produces "1/4/2128 11:30" because Excel does not understand that you want to add two times on the same day. To produce the correct result, format the first cell as a date and the second cell as a time: "1/1/2014 5:30" added to "6:00" produces the expected "1/1/2014 11:30." As with entering times, Excel automatically selects these formats correctly if you enter the times and dates as shown here, but when working with existing data you might need to select the formats from the Format Cells window.

Advertisement

Advertisement

Understanding Serial Numbers

Excel stores time and date data using a serial number that counts the number of days starting with "1" equaling midnight on January 1, 1900. For example, "January 2, 1900, 12:00 PM" equates to "2.5." On Macs, Excel starts from January 1, 1904 instead because very old Macs could not read dates earlier than 1904. Thanks to time and date cell formats, you don't normally need to work with the serials directly, but keep them in mind -- if you suddenly see an unexpected string of numbers when working with times, double check the cell-formatting options. If you copy or link data between Windows and Mac Excel workbooks, you need to change the serial number format to avoid incorrect dates. Open the "Advanced" tab in the Options and check or uncheck the "Use 1904 Data System" box. This option affects the entire workbook.

Advertisement

Version Information

Information in this article applies to Excel 2013, 2010 and 2007,as well as Excel for Mac 2011. It may vary slightly or significantly with other versions.

Advertisement

Advertisement

references & resources