How to Compare Hours in Excel With Formulas and Formatting

Formulas & functions · Updated November 20, 2026

To compare two times in Excel, enter them as real time values and use a comparison formula such as =IF(A2>B2,"A is later",IF(A2<B2,"B is later","Same time")). For durations, compare the duration cells directly; to calculate elapsed time, subtract the earlier time from the later one and format the result as a duration. These methods help you check schedules, planned versus actual hours, and shift lengths without mistaking a displayed time for its underlying value.

What You’ll Need

  • Excel for Microsoft 365 on Windows, with two columns containing times or durations.
  • Consistent, valid time entries, such as 9:15 AM and 5:30 PM. You can enter 24-hour times such as 09:15 and 17:30 too.
  • A clear idea of what “compare” means for your task: which clock time is later, which duration is longer, or how much time elapsed.
  • Optional: a result column for formulas and a copy of the workbook if you plan to change many values.

Step-by-Step

  1. Set up the times in columns. Put the first time in A2 and the second in B2, with labels such as “Scheduled” and “Actual” in row 1. Select the cells and choose Home > Number Format > Time, or press Ctrl+1 and choose a time format. On Mac, use Command+1 to open Format Cells. Formatting makes entries easier to read, but it does not convert text into valid times by itself. If you type a value and it stays aligned like text or refuses to change format, check that Excel recognized it as a time before proceeding.
  2. Compare which time of day is later. With the two clock times in A2 and B2, enter the following in C2, then press Enter:
    =IF(A2>B2,"A is later",IF(A2<B2,"B is later","Same time"))
    For A2 = 9:15 AM and B2 = 9:00 AM, the result is “A is later.” The formula compares the underlying time values, not how the cells look on screen. Replace the words in quotation marks to suit your report, or use a simpler TRUE/FALSE result with =A2>B2 when you only need to know whether A2 is later. This comparison treats the entries as times on the same day; it cannot infer that an early-morning time belongs to the following date.
  3. Compare two durations. If C2 contains planned hours and D2 contains actual hours, enter this in E2 to label which duration is longer:
    =IF(D2>C2,"Actual is longer",IF(D2<C2,"Planned is longer","Equal"))
    For a planned duration of 7:30 and actual duration of 8:15, the result is “Actual is longer.” Duration values are also stored as numbers, so a direct comparison works when both cells represent durations in the same way. If your hours are entered as decimal numbers, such as 7.5 and 8.25, compare those numbers directly and label the column clearly so they are not confused with clock times.
  4. Calculate the difference between two same-day times. With the start time in A2 and end time in B2, put this in C2:
    =B2-A2
    For a start of 9:00 AM and an end of 5:30 PM, the result is 8:30, meaning eight hours and thirty minutes. Select C2, press Ctrl+1, choose Custom, and enter [h]:mm as the format. The square brackets allow the hour total to keep increasing past 24. On Mac, use Command+1 for the same formatting dialog. If you want decimal hours, use this formula with the same start and end cells:
    =(B2-A2)*24
    That example returns 8.5. Format the result as Number rather than Time.
  5. Handle a shift that crosses midnight. If A2 is 10:00 PM and B2 is 6:00 AM the next morning, ordinary subtraction returns a negative value because the end clock reading is smaller. With those cells holding start and end times, use:
    =MOD(B2-A2,1)
    Format the result as [h]:mm. It returns 8:00 for this example. To return decimal hours instead, use:
    =MOD(B2-A2,1)*24
    This returns 8. The MOD approach assumes the elapsed shift is less than 24 hours and that the end time is on the next day when it appears earlier than the start. For multi-day events, enter full date-and-time values and subtract them instead; that preserves the actual number of days.
  6. Highlight the later or longer value visually. To highlight later clock times in B2:B20, select that range and choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter a reference to the corresponding A-cell only if you are applying a formula-based rule; for row-by-row comparisons, choose Home > Conditional Formatting > New Rule and select the rule type that uses a formula. With A2 as the comparison time and B2 as the selected first cell, enter:
    =B2>A2
    Choose a format and confirm. Because the references are relative, the rule checks B3 against A3 on the next row, and so on. On Mac, the Conditional Formatting controls are on the Home tab as well, though the dialog layout can differ. If you only need a text result, a formula column is usually easier to audit than color alone.
  7. Fill the formula down and check a few rows. Select the cell containing your formula and drag the small fill handle at its lower-right corner down the result column, or double-click the handle to fill beside a continuous data range. Confirm that the row references changed correctly and that each result matches a sample you can check by hand. For example, compare a 9:00 AM start and 5:00 PM finish: the same-day elapsed result should be 8:00, not 8:00 AM. Keep the original time columns intact so you can trace unexpected results back to the entries.

Common Mistakes

  • Comparing text instead of time values. An entry imported from a report may look like 9:00 AM but be stored as text. A format change will not repair it. Convert the entries to real Excel times before relying on comparisons.
  • Confusing a clock time with a duration. 8:00 AM is a point in the day; 8:00 formatted as a duration means eight hours. Label columns and choose formats to make the distinction clear.
  • Ignoring midnight. Directly subtracting 10:00 PM from 6:00 AM gives a negative result. Use a midnight-aware calculation for a one-night shift, or include dates for longer spans.
  • Using h:mm for totals over 24 hours. That format can make accumulated hours wrap around. Use [h]:mm to show total elapsed hours.
  • Expecting display rounding to change a comparison. Two cells may look identical because seconds or fractions are hidden, while their underlying values differ slightly. Increase the displayed precision or compare rounded values if the task requires a particular precision.

Tips

  • Use real date-and-time values when the date matters, especially for overnight work, travel, or events spanning several days. A time-only entry does not tell Excel which day it belongs to.
  • Choose one entry style for a column. Excel can usually interpret entries like 6:30 PM and 18:30, but mixed styles and imported text make a sheet harder to check.
  • Apply a clear number format after entering formulas: use a clock format for a time of day, [h]:mm for an accumulated duration, and Number for decimal hours.
  • Use a tolerance when tiny timing differences should count as equal. For example, with times in A2 and B2 and a tolerance of one minute, compare the difference to 1/1440 of a day rather than treating every fraction of a second as meaningful.
  • Keep a separate result column for labels such as “Later,” “Earlier,” or “Equal.” This makes filters and summaries easier than relying only on cell color.
  • If your organization records billable or paid hours, check its rounding and break rules before interpreting a calculated difference as an official total.

Troubleshooting

The formula returns an error or does not calculate. Check whether the inputs are valid time values rather than text. Select a suspect cell, change its number format to General, and see whether a numeric value appears. If it remains a text string, re-enter it as a time or use Excel’s import and conversion tools to turn the source data into values. Also check that the formula begins with an equals sign and that the cell is not formatted as Text; if it is, change the format to General and re-enter the formula.

The result is ######. The column may be too narrow, or a negative time may not display under the selected date system and format. Widen the column first. For a shift crossing midnight, use the MOD calculation above or include the actual dates instead of accepting a negative subtraction.

The total looks wrong after 24 hours. Select the result, open Format Cells with Ctrl+1 (Command+1 on Mac), choose Custom, and apply [h]:mm. If the result is meant to be a decimal number of hours, multiply elapsed days by 24 and use Number format instead.

Rows are highlighted incorrectly. Reopen the conditional formatting rule and verify that its formula starts with the correct row for the top cell in the selected range. A rule applied to B2:B20 should generally refer to row 2, not a fixed row, so relative references adjust as the rule evaluates each row. Also confirm that the compared cells contain times in the same context.

Frequently Asked Questions

Can Excel compare times directly?
Yes. Excel stores recognized times as fractions of a day, so operators such as greater than, less than, and equal to can compare them. The comparison is meaningful only when the values use the same context, such as times on the same day.

How do I compare hours in decimal format?
Enter hours as numbers, such as 7.5 and 8.25, and compare the cells directly. To convert an elapsed time stored as a fraction of a day to decimal hours, multiply the difference by 24, then format the result as Number.

How do I compare times when a shift crosses midnight?
For a shift under 24 hours with only start and end times, calculate the elapsed time with =MOD(B2-A2,1) and format it as [h]:mm. If you have dates as well as times, subtract the complete date-and-time values instead.

Why does Excel say two times are different when they look the same?
The cells may contain different seconds or fractional seconds that the chosen display format hides. Show more precision or decide on an acceptable tolerance. For a tolerance of one minute, the time difference is 1/1440 of a day.

Can I compare hours and return only TRUE or FALSE?
Yes. With the values to compare in A2 and B2, enter =A2>B2 to return TRUE when A2 is later or larger, and FALSE otherwise. Reverse the cell references if you need to test whether B2 is greater.

Does this work in Excel for Mac or the web?
The comparison formulas and time formats work in current Excel for Mac and Excel for the web. On Mac, Command+1 opens Format Cells; Windows uses Ctrl+1. Ribbon and dialog layouts may vary slightly by platform.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.