Excel Time Format Not Working? Fix It Step by Step

If Excel time formatting is not working, first check whether the cell contains a real time value or text: formatting changes how a value looks, but it cannot turn unrecognized text into a time. Select the cells and press Ctrl+1 on Windows, choose a time format under Number, and click OK; if the display still does not change, convert the entries to real time values before formatting them.

How Excel stores time

Excel stores a time as a fraction of a 24-hour day. Midnight is 0, noon is 0.5, and 6:00 a.m. is 0.25. A date is stored as a whole-number serial value, so a cell can contain a date and a fractional time together. The number format controls which parts of that underlying value appear on screen; it does not change the stored value or perform a conversion.

This explains a common surprise: typing 0.5 and applying a time format displays noon, while typing 12:00 into a General-formatted cell may be recognized as a time. Typing 7.5 does not mean 7:30. It is a number equal to seven and a half days; a time format may show noon because the fractional part is one half. To enter 7:30 a.m. as a decimal day fraction, the value would be 0.3125, though entering 7:30 AM directly is usually clearer.

Formatting can also make a correct value appear wrong. A cell containing 1.5 days formatted as h:mm displays 12:00, because the h format shows the time of day and hides the whole day. Use an elapsed-hours format when you need to show total hours rather than the clock time.

Check whether the entry is text or a number

Look at the cell alignment and the formula bar, but treat these only as clues: alignment can be changed manually, and a formula may return text. A more reliable check is to test the cell with a simple formula. With a suspected time in A2, enter =ISNUMBER(A2) in another cell. TRUE means A2 contains a number, which can be formatted as a time; FALSE usually means it is text or blank. A valid time stored as a number can still have an unsuitable display format.

Text commonly comes from imported CSV files, copied web tables, apostrophes typed before values, or entries that do not match the computer’s regional conventions. For example, an import may contain the characters 6:30 PM rather than a numeric time value. Applying h:mm to that text will not make Excel interpret it as a time.

A leading apostrophe, such as ‘6:30 PM, forces an entry to be stored as text.
Spaces before or after a time can prevent conversion, especially when data comes from another system.
Some imports contain nonbreaking spaces or other invisible characters that ordinary trimming may not remove.
A value such as 25:15 may be intended as an elapsed duration, not a time of day. Decide which meaning applies before converting it.

Convert text entries into real times

For a small number of cells, select a cell, remove any leading apostrophe or unwanted spaces, and re-enter the value in a form Excel recognizes, such as 6:30 PM or 18:30. For a whole column, a formula gives you a visible conversion result that you can check before replacing the original data.

With the text 6:30 PM in A2, enter =TIMEVALUE(A2) in B2. TIMEVALUE returns Excel’s numeric time value, approximately 0.7708333 for 6:30 p.m. Format B2 as h:mm AM/PM to display 6:30 PM. The formula works when the text is a time Excel recognizes under the current regional settings; it will return an error for some malformed or ambiguous strings.

For a column of entries, fill the formula down, inspect the converted results, and then copy the results and use Paste Special > Values if you need to replace the source text. Keep a copy of the original column until the conversion is verified. If the values include dates as well as times, TIMEVALUE alone discards the date portion; choose a conversion method that preserves the full date and time instead.

You can also try Data > Text to Columns on a text column. In the wizard, proceed through the delimiter screens without selecting a delimiter that would split the values, leave the column format as General, and finish. Excel may recognize the entries using your regional settings. Check the output, because this method is not a guarantee for inconsistent or ambiguous imported text.

Choose a format that matches what you want to see

On Windows desktop Excel, select the cells, press Ctrl+1, and use the Number tab in the Format Cells dialog. Choose Time for common clock displays, or choose Custom to enter a specific format code. The Mac shortcut to open Format Cells is Command+1. In Excel for the web, use the number-format controls on the Home tab; the exact options can differ from desktop Excel.

h:mm AM/PM displays a clock time such as 6:30 PM.
HH:mm displays a 24-hour clock time such as 18:30.
h:mm:ss AM/PM includes seconds in a 12-hour display.
[h]:mm displays total elapsed hours and minutes, including values above 24 hours.
[h]:mm:ss displays total elapsed hours, minutes, and seconds.

In custom formats, h represents hours, m represents minutes or months depending on context, and s represents seconds. Excel interprets m as minutes when it appears in a time context, such as between hours and seconds. Square brackets around h indicate elapsed hours rather than the hour on a 24-hour clock. For example, a duration of 27 hours and 15 minutes appears as 3:15 with h:mm, but as 27:15 with [h]:mm.

Fix totals that reset after 24 hours

If a sum of durations shows a clock time or seems to lose a day, the value may be correct while the format hides the total. For example, with durations 8:15, 7:50, and 9:10 in A2:A4, enter =SUM(A2:A4) in A5. The total is 25 hours and 15 minutes. Format A5 as [h]:mm to display 25:15; a standard h:mm format displays 1:15 because it shows the time-of-day portion of the 25-hour value.

This is one of the most frequent reasons people think a time formula is broken. The brackets are not decoration: they tell Excel to accumulate the hours instead of wrapping the display at 24. Use this format for work logs, project durations, travel times, and any other total that can exceed one day. If a total should be expressed as decimal hours instead, multiply the numeric duration by 24 and format the result as a number; do not expect a clock-time format to show decimal hours.

Handle calculations that cross midnight

Subtracting an end time from a start time can produce a negative result when a shift crosses midnight. If the start time is in A2 and the end time is in B2, enter =MOD(B2-A2,1) in C2. With A2 equal to 10:00 PM and B2 equal to 12:30 AM, the result is 2 hours and 30 minutes. Format C2 as h:mm. MOD wraps the difference to the portion of a day, which is useful for a single overnight interval.

That formula assumes the interval is less than 24 hours. If the start and end include dates, subtract the full date-time values instead so Excel can calculate longer intervals accurately. If you use clock times alone for a period that might last more than a day, the dates are necessary to distinguish, for example, a 2-hour interval from a 26-hour interval.

Excel’s default date system does not display negative time results as ordinary clock times in the same way it displays positive values. A row of hashes (####) can indicate that a column is too narrow, but it can also appear when a date or time result is negative. Widen the column first; if the value remains hidden, check the calculation and its date assumptions rather than assuming the display width is the only issue.

Check regional settings and imported data

Excel interprets typed and imported times according to recognized formats and regional settings. A value using a period, AM/PM marker, or time separator unfamiliar to the current locale may be treated as text or parsed differently than expected. Ambiguous entries such as 03/04/2026 are especially risky because the date order can vary. For shared workbooks, use unambiguous dates and a consistent time convention, and verify a sample after importing.

When importing a CSV, the Windows regional settings and the import process can affect how dates and times are interpreted. Avoid opening a file and assuming every displayed value was parsed correctly. If the source has a known structure, use Excel’s import controls to inspect the column before loading it, or import the column as text and convert it deliberately. A time-zone offset is a separate issue: Excel’s ordinary time values do not carry a time zone, so formatting a time cannot convert it between zones or account for daylight-saving changes.

Diagnose other common display problems

The cell shows 00:00. The underlying value may be zero, or a formula may return zero because its inputs are blank or invalid. Check the formula bar and trace the referenced cells.
The cell shows ####. Widen the column. If that does not help, look for a negative date or time result.
Seconds are missing. Choose a format that includes seconds, such as h:mm:ss, and check whether the underlying value actually contains nonzero seconds.
The display changes but the calculation does not. Number formatting changes appearance, not the numeric value. If you need rounding, use a calculation that rounds the value rather than hiding decimal precision through formatting.
Only some cells respond. The range may mix numeric times and text, or cells may have different formats. Check representative cells and convert the text entries consistently.

If a cell has a formula, also inspect the formula bar. A formula can return a number, a text string, or an error. A time format can display a numeric result as a time, but it cannot turn a text result such as “6:30 PM” into a numeric time. If the formula is intended to calculate a time, make sure its inputs and operations produce a number.

Frequently Asked Questions

Why does Excel show a number instead of a time? The cell is probably displaying the underlying serial value in General or Number format. Apply a time format with Ctrl+1 on Windows or Command+1 on Mac. If the number does not represent the intended time, check how it was entered before changing the display.

Why does my total time reset at 24 hours? A format such as h:mm displays the clock-hour portion and wraps after 24 hours. Apply the custom format [h]:mm to show accumulated hours, for example 25:15 instead of 1:15.

Why will Excel not format a time copied from a website? The copied value may be text, may include extra spaces, or may use a time notation Excel does not recognize under your regional settings. Test it with ISNUMBER, clean or re-enter the value, or convert recognized time text with TIMEVALUE.

How do I show AM or PM? Apply the custom format h:mm AM/PM. The stored value remains the same; the format adds the 12-hour clock marker to its display.

Can Excel display a duration longer than one day? Yes. Store the duration as a numeric time value and use an elapsed format such as [h]:mm or [h]:mm:ss. For intervals that cross midnight, include dates when a duration could exceed 24 hours.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.