To combine hours and minutes stored in separate cells, divide the hours by 24 and the minutes by 1,440, then format the result as [h]:mm. For example, if the hours are in A2 and the minutes are in B2, enter =A2/24+B2/1440 in C2. With 2 in A2 and 45 in B2, the result displays as 2:45. You will need a worksheet with the two input values and a cell for the result; the steps below show how to set it up and handle totals longer than a day.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The same formulas work in Excel for Mac and Excel for the web, although the ribbon and keyboard shortcuts can differ slightly.
- Two cells containing numeric values: one for hours and one for minutes. For example, enter 2 in A2 and 45 in B2. The input values can be whole numbers; minutes can also exceed 59 if they represent extra elapsed minutes.
- A blank cell for the combined result. If you are combining many rows, allow space for a result formula on each row.
Step-by-Step
- Enter the hours and minutes in separate cells. Put the hour amount in A2 and the minute amount in B2. Use numbers such as 3 and 20, rather than typing labels such as 3 hours or 20 minutes into those cells. Labels make entries text, which Excel cannot reliably use in time arithmetic. You can add headings in A1 and B1, such as Hours and Minutes, to make the columns clear.
- Choose a cell for the combined time. Click C2, or another empty cell on the same row. Keeping the result next to the inputs makes it easier to fill the formula down if you have a list. Check that the result cell is not merged with another cell and does not already contain a value you need.
- Enter the conversion formula. With hours in A2, minutes in B2, and the result in C2, enter
=A2/24+B2/1440. Excel stores time as a fraction of a 24-hour day, so dividing hours by 24 and minutes by 1,440 converts both amounts to the same unit before adding them. Press Enter. If A2 is 2 and B2 is 45, the stored result is 0.1145833 of a day, equivalent to 2 hours and 45 minutes. - Apply an elapsed-time format. Select C2, then go to Home > Number format dropdown > More Number Formats. In the Format Cells window, select Custom, enter
[h]:mmin the Type box, and choose OK. The square brackets tell Excel to show cumulative hours instead of restarting the hour count at 1 after 24 hours. In Excel for Mac, open Format Cells from the Home tab’s number-format controls or the Format menu, then use the Custom category. Excel for the web also offers custom number formats through its number-format controls, though the dialog layout may vary. - Check the result against the inputs. For 2 hours and 45 minutes, C2 should show 2:45. For 1 hour and 75 minutes, the same formula and format show 2:15 because the extra 60 minutes become another hour. That automatic carry is useful when minutes are not limited to 0 through 59.
- Copy the formula for additional rows. If each row has hours in column A and minutes in column B, select C2 and drag the fill handle—the small square at the lower-right corner of the cell—down the column. Excel adjusts the references for each row. For example, the next row will refer to A3 and B3. Check a couple of rows after filling to confirm the references and input columns are correct.
- Add several combined durations when needed. If your combined durations are in C2 through C10, select a total cell and enter
=SUM(C2:C10). This assumes each result cell contains an Excel time value, not text, and that the total cell is formatted as[h]:mmtoo. For instance, if the durations are 2:45 and 3:30 and the remaining rows are blank, the sum is 6:15. The SUM function ignores blank cells. - Decide whether you need a duration or a clock time. The division formula creates an elapsed duration, so it is appropriate for work hours, task lengths, or time spent. If you instead want a time-of-day display that wraps around after midnight, use a clock-time formula. With hours in A2, minutes in B2, and the intended result in C2, enter
=TIME(A2,B2,0)and format C2 ash:mm AM/PMorh:mm. For example, 2 and 45 display as 2:45, but 27 and 0 wrap to 3:00. Use the elapsed-time method when the 27 hours must remain visible as 27:00.
Common Mistakes
Using ordinary addition on the raw numbers. With hours in A2 and minutes in B2, a formula such as =A2+B2 adds 2 and 45 to get 47, not 2 hours and 45 minutes. Hours and minutes are different units. Convert them to fractions of a day with the formula above, or convert both to minutes before doing a separate calculation.
Formatting only as h:mm when the total can reach 24 hours. The format h:mm displays the hour on a clock and wraps after 23:59. A total of 27 hours can therefore appear as 3:00, which may look like a calculation error. Choose [h]:mm for accumulated durations. The brackets are important; they prevent the displayed hour count from resetting each day.
Typing units into the input cells. Values such as 2 hrs, 45 min, or 2:45 may be treated as text or as a time value rather than the numeric hour and minute amounts the formula expects. Keep the inputs as plain numbers, and put descriptive headings in adjacent cells. If you already have text entries, replace them with numeric values before relying on the calculation.
Mixing durations with times of day. A duration of 8 hours and a clock time of 8:00 may look similar, but they are used differently in calculations. Decide whether the value means an amount of time or a point on the clock, then use a consistent format and method. In particular, do not use a wrapping clock-time result to report a long work duration.
Expecting a number format to change the underlying value. Formatting changes how a value appears, not the calculation stored in the cell. If you see a decimal such as 0.114583 instead of 2:45, the arithmetic may already be correct; apply the custom elapsed-time format. Conversely, a pleasing display does not prove the input values are correct.
Tips
- Use a clear header for every column, and consider naming the result column Combined duration. This helps distinguish it from a start time or finish time when a worksheet contains several kinds of time data.
- Format the result column before filling formulas down. Select the whole column or the expected result range and apply
[h]:mm; new results will then be easier to read and compare. - Keep hours and minutes numeric if you may need to sort, sum, or compare the results. A formula that joins values into a display string can look tidy but may no longer behave like a usable time value.
- Minutes do not have to be below 60 in the elapsed-duration formula. For example, 4 hours and 90 minutes correctly become 5:30. Still, if the values are intended to describe a conventional time entry, check whether a value such as 90 indicates a typing mistake.
- To make the formula easier to inspect, keep the input columns adjacent to the result and avoid inserting unrelated columns between them. If you move columns later, Excel normally updates cell references, but it is still sensible to verify the formula after structural changes.
- On Windows, Ctrl+1 opens Format Cells for the selected cell. On Mac, Command+1 opens Format Cells in Excel. If your keyboard or Excel version handles the shortcut differently, use Home > Number format dropdown > More Number Formats instead.
Troubleshooting
The result is a decimal rather than hours and minutes. Select the result cell and apply the custom format [h]:mm. If the value is still unexpected, confirm that the formula refers to the intended hour and minute cells and that the inputs are numbers. A decimal fraction is normal internally: Excel represents one full day as 1.
The total appears to reset after 24 hours. The result cell is probably using h:mm or another clock format. Open Format Cells, choose Custom, and use [h]:mm. If you are summing a range, apply the format to the total cell as well as to the individual duration cells.
The formula returns an error or does not calculate. Check for text in the input cells, misspelled cell references, and an accidental apostrophe before the formula. A formula should start with an equals sign. If Excel displays the formula itself rather than a result, change the cell from Text to General, then re-enter the formula. Also check that Show Formulas has not been turned on from the Formulas tab.
The displayed time is wrong by a large amount. Verify the units and locations: hours belong in the cell referenced as hours, and minutes in the cell referenced as minutes. For the example layout, the formula must use A2 for hours and B2 for minutes. If your columns are reversed, reverse the references. Also check that you have not entered a clock time such as 2:45 into a cell that the formula expects to contain the number 45.
Only some rows calculate correctly. Click a result cell that is wrong and inspect its formula in the formula bar. When copying down, relative references should advance one row at a time. If the formula points to the same input row repeatedly, remove unintended dollar signs or copy the formula from the correct starting row. Confirm the input cells are populated and are not stored as text.
Frequently Asked Questions
Can I combine hours and minutes if the minutes are over 59?
Yes. The elapsed-time formula converts every minute into a fraction of a day, so 2 hours and 75 minutes display as 3:15. If values above 59 are not expected in your data, check them for entry errors.
How do I show a total longer than 24 hours?
Format the cell as [h]:mm, including the square brackets. Use that format for both the combined durations and any SUM total. Without brackets, Excel may show only the time remaining on a 24-hour clock.
Can I enter 2:45 directly instead of using separate columns?
Yes, if the entry is meant to be a duration and Excel recognizes it as a time value. Format the cell as [h]:mm when totals may exceed a day. Separate numeric hour and minute columns are useful when the values are collected independently or need separate validation.
Should I use TIME or divide by 24 and 1,440?
Use TIME when you want a clock-time value that wraps after 24 hours. Use division when you need an elapsed duration that can display totals such as 27:00. The choice depends on whether the result is a point on the clock or a length of time.
Does this work in Excel for Mac and Excel for the web?
Yes. The formulas and the [h]:mm format work in current Excel for Mac and Excel for the web. The available buttons and dialog layout can differ, so use the number-format controls or Format Cells if a ribbon path looks different.
Can I add the combined results for a whole column?
Yes. Use SUM on the result range, such as C2:C10, and format the total as [h]:mm. Make sure the cells contain numeric Excel time values rather than text produced by a text-joining formula.
Related
- Center Align in Excel Without Merging Cells: How To
- How to Center a Page Horizontally in Excel for Printing
- How to Center a Table on a Page in Excel for Printing
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Extract Text from an Image in Excel with Data from PictureTo extract a table from a photo or screenshot in Excel for Microsoft 365 on Windows, choose Data…
- Excel Named Range Scope Greyed Out? How to Change ItIn Excel for Microsoft 365 on Windows, a named range’s scope is fixed when the name is created,…
- Share a Google Calendar in Outlook: Step-by-StepTo see a Google Calendar in Outlook, copy its iCal address from Google Calendar and add it to…
- Conditional Formatting Excel Whole Row: A Practical GuideYes. To format a whole row in Excel based on one cell, select the rows’ data range, choose…
- Making a Calendar in Google Sheets: A Practical GuideTo make a calendar in Google Sheets, enter a date from the month you want in a cell…
- Active Print Area in Excel: Meaning and SetupIn Excel, the “active print area” usually means the cell range currently designated to print on a worksheet;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.