Excel Time Conversion Formula: Hours, Minutes, and More

If A2 contains an Excel time, multiply it by 24 to get decimal hours: =A2*24. For decimal minutes, use =A2*1440, and for decimal seconds, use =A2*86400; choose the multiplier that matches the unit you need.

How Excel Stores Time

Excel represents a time as a fraction of a 24-hour day. Midnight is 0, noon is 0.5, and 6:00 PM is 0.75. A value such as 6:00 AM is one quarter of a day, or 0.25. This numeric representation is why simple multiplication can convert a time to hours, minutes, or seconds.

What you see in a cell is controlled by its number format, not necessarily by the value stored in it. A cell containing 0.5 might display as 12:00 PM if it has a time format, or as 50% if it has a percentage format. Changing the format changes the display; it does not convert the underlying value. To convert units for a calculation, use a formula as well as an appropriate format.

Excel can also store a date and time together. The whole-number portion represents the date, while the fractional portion represents the time of day. For example, subtracting two full date-and-time values can return an elapsed duration that includes both the date and time difference. This distinction matters when a calculation spans midnight or more than one day.

Convert a Time to Decimal Hours, Minutes, or Seconds

With a time in A2, multiplying by the number of units in one day gives the corresponding number of units. Use the formula that matches the result you want:

Decimal hours: With 2:30 AM in A2, use =A2*24. The result is 2.5, because two hours and 30 minutes equal 2.5 hours.
Decimal minutes: With 2:30 AM in A2, use =A2*1440. The result is 150, because 24 hours times 60 minutes equals 1,440 minutes per day.
Decimal seconds: With 2:30 AM in A2, use =A2*86400. The result is 9000, because 2.5 hours times 3,600 seconds equals 9,000 seconds.

For these examples, format the result cells as General or Number so Excel displays the numeric conversion. If a result still appears as a clock time, select the result cell and choose Home > Number Format dropdown > General. A time format can make a converted number look confusing even when the calculation is correct.

If you need whole minutes rather than a decimal, round the converted result. With a time in A2 equal to 1:35, use =ROUND(A2*1440,0); the result is 95 minutes. The calculation is 1 hour times 60, plus 35 minutes. Rounding is useful when seconds are present and the output must be an integer; decide whether rounding or truncating is appropriate for the task.

Convert Decimal Hours into an Excel Time

To turn a decimal number of hours into a time value, divide by 24. With 2.5 in A2, use =A2/24; the result is the time value 2:30 AM when displayed with a clock format. The arithmetic is 2.5 divided by 24 of a day. Format the result as h:mm or h:mm AM/PM to show it as a time.

This is a conversion to a time-of-day value, so ordinary clock formats wrap after 24 hours. If A2 contains 27.5, the formula represents 27.5 hours as a day plus 3.5 hours. A format such as h:mm displays only the clock portion, 3:30, and does not show the full 27-hour duration. Use a duration format when the number means elapsed hours rather than a time on a clock.

For a value stored as a number of seconds, convert it to an Excel time value by dividing by 86,400. With 5,400 in A2, use =A2/86400; the result is a duration of 1 hour and 30 minutes. Format the result as [h]:mm:ss to show 1:30:00. The brackets allow hours to accumulate beyond 24 if the duration is longer.

Build a Time from Separate Hour, Minute, and Second Values

If a worksheet has separate numeric fields for hours, minutes, and seconds, Excel’s TIME function can assemble them. With hours in A2, minutes in B2, and seconds in C2, and with 14, 5, and 9 in those cells, use =TIME(A2,B2,C2). The result is 2:05:09 PM when formatted as h:mm:ss AM/PM.

TIME is useful for normalizing components: for example, 90 minutes is carried into an additional hour. However, it returns a time-of-day fraction and wraps hours after 23. Thus it is not the right method for preserving an elapsed duration of 27 hours. For accumulated hours, keep the total as a duration and display it with the [h] format instead.

Convert Time Stored as Text

Imported data sometimes looks like a time but is stored as text. A clue is that Excel may leave the entry left-aligned by default, or arithmetic may return an error. If A2 contains text Excel recognizes as a time, such as 2:30 PM, use =TIMEVALUE(A2). The result is the numeric fraction 0.6041667, which displays as 2:30 PM when the formula cell is formatted as a time.

TIMEVALUE converts a recognized time string into a time value; it does not extract hours as a decimal number. To get decimal hours from text, place the conversion inside a multiplication. With the same text in A2, use =TIMEVALUE(A2)*24; the result is 14.5. This represents 14 hours and 30 minutes. If the text is not recognized, check for extra spaces, inconsistent separators, or a format that does not match the computer’s regional settings. TIMEVALUE interprets text according to the applicable locale, so a string accepted on one system may need adjustment on another.

When a column contains a mixture of text and numeric times, standardize the values before relying on calculations. A formula that works for one recognized text value may not resolve every kind of imported string. Check representative rows and confirm that the converted values behave numerically, for example by formatting a converted value as General.

Calculate Elapsed Time and Durations

For start and end times on the same day, subtract the start from the end. With a start time in A2 of 9:15 AM and an end time in B2 of 5:45 PM, use =B2-A2; the result is 8:30, meaning eight hours and 30 minutes. Format the result as h:mm or [h]:mm. To display the elapsed duration as decimal hours instead, multiply the difference by 24: with those same entries, =(B2-A2)*24 returns 8.5.

A simple subtraction gives a negative result if the end clock time is earlier than the start. That often happens for a shift that crosses midnight. With a start time of 10:00 PM in A2 and an end time of 6:00 AM in B2, use =MOD(B2-A2,1); the result is 8:00, or eight hours. The MOD calculation wraps the negative clock-time difference into the remaining part of the day.

Use that midnight-wrap method only when the entries are clock times and the interval is known to be less than 24 hours. If the start and end include actual dates, subtract the full date-and-time values instead. For instance, a shift beginning Monday night and ending Wednesday morning should include both dates in the source cells; otherwise a clock-only formula cannot know whether the interval lasted eight hours or 32 hours.

Choose a Number Format That Shows the Right Result

Formats determine whether a value reads as a clock time, a duration, or a plain number. Common choices include:

h:mm AM/PM for a time of day such as 2:30 PM.
h:mm for a compact clock display such as 14:30, depending on the underlying value and format.
[h]:mm for elapsed hours and minutes that may exceed 24 hours.
[h]:mm:ss for durations where seconds also matter.
General or Number for decimal hours, minutes, or seconds.

In Excel for Microsoft 365 on Windows, select the cells and press Ctrl+1 to open Format Cells. Choose the Number tab, then Custom, and enter a format such as [h]:mm. On Mac, Command+1 opens Format Cells. In Excel for the web, the available format controls and dialog may differ, but number formatting is still separate from the formula: if a particular custom format is not convenient in the web interface, open the workbook in the desktop app to set it.

Square brackets around h are important for durations. Without brackets, an hours format displays the hour on a 12- or 24-hour clock and repeats after a full day. With [h], Excel displays cumulative hours, so a 30-hour duration can appear as 30:00 rather than 6:00.

Common Problems and How to Fix Them

The answer looks like a date or clock time. The result cell probably inherited an unsuitable format. Change it to General or Number for a decimal conversion, or choose a duration format for elapsed time.
A duration above 24 hours appears too small. Use a bracketed hour format such as [h]:mm. A normal h:mm format cycles around the clock.
Subtraction gives a negative value. If the time crosses midnight and the cells contain clock times only, a wraparound calculation can be appropriate. If the cells should represent real dates and times, include the dates rather than hiding a data-entry problem with a wraparound formula.
A conversion returns an error or zero. The source may be text, blank, or an unrecognized time string. Confirm the cell contents and test whether Excel can parse the value as a time.
A result is off by a factor of 60 or 24. Check the target unit. Multiplying by 24 gives hours, by 1,440 gives minutes, and by 86,400 gives seconds. Dividing by 24 converts decimal hours into Excel’s day-based time value.

Also distinguish between time of day and elapsed time. The entry 1:00 can mean 1 AM on a clock or a duration of one hour. Its stored fraction is the same; the meaning comes from the worksheet context and how the value is formatted. Label columns clearly, for example Hours, Clock time, or Elapsed duration, so later users do not mistake one interpretation for another.

Which Conversion Should You Use?

Choose a conversion based on what the next calculation or report requires. Decimal hours are practical for multiplying by an hourly rate or calculating average time, while h:mm is easier for people reading a schedule. Decimal minutes are useful when a process is measured in minutes, and [h]:mm:ss is useful for tracking cumulative durations.

Before selecting a formula, ask whether the source is a true Excel time, a text string, a time of day, or a duration. A numeric time can be converted by multiplication; recognized time text may need TIMEVALUE; separate components can be combined with TIME; and elapsed intervals are generally calculated by subtraction. Then set the result’s number format to match the intended unit. This sequence prevents the common mistake of changing a display format and assuming the underlying number has been converted.

Frequently Asked Questions

What is the Excel formula to convert time to hours?
With a numeric Excel time in A2, multiply by 24: =A2*24. For example, 1:30 becomes 1.5 decimal hours. Format the result as General or Number to see the decimal.

How do I convert Excel time to minutes?
With the time in A2, use =A2*1440, since a day contains 1,440 minutes. A value of 1:30 returns 90 minutes. If you need a whole number and the source includes seconds, round the result as needed.

How do I convert decimal hours to hours and minutes?
With decimal hours in A2, divide by 24 using =A2/24, then format the result as h:mm. A value of 1.75 becomes 1:45. For totals that may exceed 24 hours, use [h]:mm instead.

Why does Excel show 0.5 for 12:00 PM?
Excel stores noon as half of a day, or 0.5. The cell’s number format determines whether it displays as 0.5 or 12:00 PM. The underlying value has not changed just because you changed its display format.

How do I calculate hours when a shift ends after midnight?
If the cells contain clock times only and the shift is under 24 hours, subtract with a wraparound calculation such as =MOD(B2-A2,1). If the shift can last longer or the dates are known, include full date-and-time values and subtract those instead.

How do I show a total of more than 24 hours?
Keep the duration as an Excel numeric value and apply the custom number format [h]:mm or [h]:mm:ss. The brackets make Excel display accumulated hours instead of restarting the clock display after each day.

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.