How to Calculate Hours in Excel: Formulas, Formats, and What to Watch For

Calculating hours in Excel sounds straightforward — until you run into negative time values, midnight crossovers, or totals that display as fractions instead of hours. Excel handles time in a specific way that's genuinely useful once you understand it, but trips up almost everyone at first.

How Excel Stores Time (This Explains Everything)

Excel doesn't store time the way you might expect. Behind every time value is a decimal fraction of a 24-hour day. Midnight is 0, noon is 0.5, and 6:00 PM is 0.75. When you type 8:00 AM into a cell, Excel stores 0.333... and displays it as a time because of the cell format applied.

This matters because:

• Addition and subtraction work naturally — you're just adding decimals
• Totals over 24 hours need special formatting or Excel resets the display to zero
• Multiplying hours by a wage rate requires converting the decimal to a real number first

Basic Hour Calculation: Start Time to End Time

The simplest formula subtracts a start time from an end time:

=B2-A2 

Where A2 is the start time and B2 is the end time. Format the result cell as h:mm and you'll see elapsed hours and minutes.

For decimal hours (like 8.5 instead of 8:30), multiply by 24:

=(B2-A2)*24 

This converts Excel's fractional day value into a plain number you can use in calculations.

Summing Hours Across Multiple Days

Add a column of time values normally with SUM:

=SUM(C2:C10) 

Here's where many users hit a wall: if the total exceeds 24 hours, Excel displays only the remainder. A 40-hour work week shows as 16:00 instead of 40:00.

The fix is a custom cell format. Select the result cell, open Format Cells → Custom, and enter:

[h]:mm 

The square brackets around h tell Excel to display the total accumulated hours rather than resetting at 24.

Handling Overnight Shifts ⏱️

A shift that crosses midnight — say, 10:00 PM to 6:00 AM — produces a negative result with basic subtraction, and Excel shows a string of #####.

Two common solutions:

Option 1 — Add 1 if the result is negative:

=IF(B2<A2, B2-A2+1, B2-A2) 

Adding 1 represents one full day (24 hours), correcting the rollover.

Option 2 — Use MOD:

=MOD(B2-A2,1) 

MOD returns the remainder when dividing by 1, which handles the negative wrap automatically. It's cleaner for large datasets.

Calculating Pay from Hours Worked

Once you have decimal hours (using *24), multiplying by an hourly rate is straightforward:

=(B2-A2)*24*C2 

Where C2 contains the hourly rate. Format this cell as currency, not time — otherwise Excel will try to display your pay as hours.

This step catches people off guard regularly. If your result looks like 12:30 when it should be $100.00, the cell format is the issue, not the formula.

Using TEXT and TIME Functions for More Control

The TEXT function lets you format a time calculation as a readable string:

=TEXT(B2-A2,"h:mm") 

Useful for display purposes, though the result becomes text, not a number you can calculate with further.

The TIME function builds a time value from separate hour, minute, and second inputs:

=TIME(8,30,0) 

This returns the Excel value for 8 hours and 30 minutes — handy when you're constructing times from data spread across multiple columns.

Common Formulas at a Glance

Variables That Affect Which Approach Works for You 🔧

Not every method suits every scenario. A few factors shape which formulas and formats make the most sense:

• Sheet version — Google Sheets handles time similarly but has minor formula differences; older Excel versions may lack certain formatting options
• Data source — times imported from CSV or external systems sometimes arrive as text strings, not true time values, requiring conversion with TIMEVALUE() before any calculation works
• Shift structure — fixed 9-to-5 schedules, rotating overnight shifts, and split shifts each introduce different edge cases
• Output goal — whether you need readable time displays, numbers for further math, or payroll-ready totals changes which formula chain to use
• Skill comfort level — MOD and custom formats are easy once seen, but unfamiliar to users who haven't encountered the 24-hour reset issue before

The Part That Depends on Your Setup

The mechanics above are consistent — Excel's time storage doesn't change. What varies is how your specific data arrives, what your totals need to feed into, and how your spreadsheet is structured. A timesheet built for a single employee looks different from one tracking a rotating team across time zones, and both differ from a simple project-hours log.

Understanding the decimal-day foundation, the [h]:mm format fix, and the overnight shift workaround covers the majority of real-world cases — but which combination applies depends on what you're actually working with.