To convert decimal hours in Excel into a time value, divide by 24; to convert an Excel time value into decimal hours, multiply by 24. For example, if A2 contains 2.5 hours, enter =A2/24 in B2 and format B2 as h:mm to display 2:30; if A2 already contains the time 2:30, =A2*24 returns 2.5 hours.
How Excel stores time
Excel does not store a time as a special clock object. It stores numbers: one whole number represents one day, and the part after the decimal point represents a fraction of a day. Midnight is zero, noon is 0.5, and 6 a.m. is 0.25. A date-time value can combine a whole-number date with a fractional time.
This system explains the conversion factors. A day has 24 hours, 1,440 minutes, and 86,400 seconds. Excel time values are therefore easy to convert once you know whether the cell holds a time fraction or an ordinary number of hours, minutes, or seconds. The display can be misleading: a cell showing 2:30 may contain the number 0.1041667, while a cell showing 2.5 may represent two and a half hours.
Formatting changes how a numeric value appears; it does not change the value itself. That distinction is central to time conversion. If a cell contains the text 2:30 rather than a numeric time, changing its number format alone will not turn it into a usable time value.
Convert decimal hours into an Excel time
Use division by 24 when a number means a quantity of hours and you want Excel to treat it as a duration. With decimal hours in A2, enter this in B2:
With A2 containing 2.5 decimal hours, enter in B2: =A2/24. The result is an Excel time value of 0.1041667 days, which displays as 2:30 when B2 uses the h:mm format.
For a longer duration, the same formula works. If A2 contains 37.5, B2 contains 1.5625 days as a serial value. Apply the custom format [h]:mm to display 37:30 rather than a clock time that wraps around after 24 hours. The square brackets tell Excel to show cumulative hours.
To apply a custom format in Excel for Microsoft 365 on Windows, select the result cell, press Ctrl+1, choose Number, then Custom, enter [h]:mm in the Type box, and select OK. On a Mac, Command+1 opens the Format Cells dialog. In Excel for the web, use the Home tab’s number-format controls; the available custom-format interface can differ from the desktop dialog.
Fractions of an hour work naturally. For example, 0.25 hours is 15 minutes, and 1.75 hours is 1 hour 45 minutes. The formula converts the quantity, while the number format determines whether the result is shown as a duration, a clock-style time, or a decimal.
Convert minutes or seconds into time
Divide a number of minutes by 1,440 or a number of seconds by 86,400 to convert the quantity into Excel’s fraction-of-a-day representation. Then choose a duration format suitable for the length of the result.
- Minutes to Excel time: divide by 1,440.
- Seconds to Excel time: divide by 86,400.
- Hours to Excel time: divide by 24.
With 90 minutes in A2, enter in B2: =A2/1440. The result is 0.0625 days, or 1 hour 30 minutes; format B2 as [h]:mm to display 1:30.
With 5,400 seconds in A2, enter in B2: =A2/86400. The result is also 0.0625 days, which displays as 1:30 using [h]:mm. If you need to show seconds, use [h]:mm:ss; the result displays as 1:30:00. A value of 90 seconds would display as 0:01:30 in that format.
Excel also has a TIME function for assembling a time from hour, minute, and second components. It is useful for clock times, but it is not the best way to represent an unrestricted duration: TIME wraps values beyond a day back into a 24-hour clock. For instance, 27 hours is better converted by dividing by 24 and formatted as [h]:mm than entered as a TIME value if you need to preserve the full 27 hours.
Convert Excel time back to hours, minutes, or seconds
Multiply an Excel time value by the number of units in a day to get a regular numeric total. Keep the result in General or Number format if you want a decimal quantity, rather than a clock display.
With an Excel time or duration in A2, enter in B2: =A2*24. If A2 represents 2 hours 30 minutes, B2 returns 2.5 hours. If A2 represents 37 hours 30 minutes, it returns 37.5.
With an Excel time value in A2, enter in B2: =A2*1440. A2 equal to 1:30 produces 90 total minutes.
With an Excel time value in A2, enter in B2: =A2*86400. A2 equal to 1:30 produces 5,400 total seconds.
These calculations are useful for timesheets, service durations, and logs where totals need to be compared or averaged as numbers. If the cell still displays as a time after multiplication, change its format to General or Number; the calculation may be right even when the display is confusing.
Choose a format for clock times and durations
A clock time answers what time of day it is. A duration answers how much time elapsed. The same underlying Excel value can be displayed differently depending on its number format, so choose a format that matches the question you are asking.
- Use h:mm or h:mm AM/PM for a time of day, such as 2:30 PM.
- Use [h]:mm for an elapsed duration that may reach or exceed 24 hours, such as 37:30.
- Use [h]:mm:ss when the duration needs to show seconds.
- Use [m]:ss to show total elapsed minutes and seconds, including totals above 60 minutes.
Without brackets, h:mm displays the hour on a 24-hour cycle. A duration of 27 hours can therefore appear as 3:00, which looks like a three-hour result. With [h]:mm it appears as 27:00. Likewise, mm:ss shows the minute component within an hour, while [m]:ss accumulates all elapsed minutes.
Formats such as [h]:mm do not convert text or change a value’s arithmetic meaning. They only control the display of a numeric value. If a converted result will be used in further calculations, retain the numeric formula result and apply the appropriate format rather than replacing it with typed text such as 2 hours 30 minutes.
Convert time stored as text
Imported CSV files and copied data sometimes contain times as text. A cell may look like 2:30 PM but fail to behave like a time in formulas. One clue is that changing the cell format has no effect, or a calculation returns an error. Confirm the cell’s contents and data type before attempting a conversion.
With text recognized as a valid time in A2, enter in B2: =TIMEVALUE(A2). If A2 contains the text 2:30 PM, the formula returns the numeric time value for 2:30 PM, which is approximately 0.6041667; format B2 as h:mm AM/PM to display it as 2:30 PM.
TIMEVALUE depends on Excel being able to interpret the text as a time in the workbook’s regional settings. A value such as 14:30 is commonly understood as 2:30 PM, but inconsistent separators, extra words, or a different locale can prevent recognition. Clean the source text or standardize its format if conversion fails. If the imported value includes a date as well as a time, decide whether you need only the time component or the full date-time value before converting it.
Do not use TIMEVALUE on a cell that already contains a valid numeric Excel time just because it looks like text on screen. In that case, use the value directly. Applying a formula to the wrong kind of input can create errors or discard information.
Calculate elapsed time across midnight
Subtracting a start time from an end time works directly when both times are on the same date. If you have times only and the shift runs past midnight, simple subtraction produces a negative value. Use MOD to wrap the difference into one day.
With a start time in A2 and an end time in B2, and both cells containing time-of-day values, enter in C2: =MOD(B2-A2,1). If A2 is 10:15 PM and B2 is 2:45 AM, C2 returns 0.1875 days, or 4 hours 30 minutes; format C2 as [h]:mm.
MOD is appropriate for a single elapsed interval shorter than 24 hours when the entries contain times but no dates. If the start and end cells contain full date-time values, subtract the start from the end instead. With the start date and time in A2 and the end date and time in B2, enter in C2: =B2-A2. If the interval is 27 hours 30 minutes, the result is 1.1458333 days and [h]:mm displays 27:30. Including dates avoids ambiguity when an interval lasts more than a day.
Common conversion problems
Many apparent calculation errors are format or input issues. Check the value, the formula, and the display separately. These are common causes:
- The result looks too small. A time is stored as a fraction of a day. A result of 0.5 may mean 12 hours, not half an hour.
- A long duration resets after 24 hours. Use [h]:mm rather than h:mm to show total elapsed hours.
- The formula returns an error or zero unexpectedly. The source may be text, blank, or formatted in a way that does not match the input. Check whether Excel recognizes it as a numeric time.
- A time difference is negative. The end time may be after midnight while the start time is on the previous evening. Use MOD for time-only entries or include actual dates.
- Negative time displays as hashes. Excel’s standard 1900 date system does not display negative date-time serials as ordinary times. Changing the workbook to the 1904 date system can affect existing dates, so do not switch systems casually just to fix a display.
Also check whether your source number means a fraction of a day or a quantity of hours. A value of 0.5 might mean half a day, which is 12 hours, or half an hour, depending on the source. Excel cannot infer the intended unit; use the correct conversion factor for what the number represents.
Practical workflow for a time-conversion sheet
A clear layout makes formulas easier to audit and reduces unit mix-ups. Keep the original data unchanged, put the converted value in a separate column, label the units, and apply a display format that communicates whether the result is a clock time or an elapsed duration.
- Identify the source unit and data type: decimal hours, minutes, seconds, a numeric Excel time, or text.
- Choose the conversion factor: divide hours by 24, minutes by 1,440, or seconds by 86,400 to create an Excel time value.
- Use multiplication by 24, 1,440, or 86,400 to get decimal totals from an Excel time value.
- Apply a format such as [h]:mm for durations that may exceed one day.
- Test the result with a known example and verify the units before filling the formula down.
For repeated records, enter the formula in the first result row and use the fill handle to copy it down. Keep the column heading explicit, such as Duration (hours) or Elapsed time (h:mm). A heading that only says Time may be ambiguous when a sheet contains both clock times and durations.
Frequently Asked Questions
How do I convert 1.5 hours to time in Excel?
Divide the value by 24. If A2 contains 1.5, enter =A2/24 in B2 and format B2 as h:mm. Excel displays 1:30.
How do I convert 2:30 to decimal hours?
If A2 contains a real Excel time value of 2 hours 30 minutes, enter =A2*24 in another cell and use General or Number format. The result is 2.5.
How do I make Excel show more than 24 hours?
Apply the custom number format [h]:mm to the duration cell. The brackets display cumulative hours, so 27 hours appears as 27:00 instead of 3:00.
Why does Excel show 0.5 instead of 12:00?
Excel stores noon as 0.5 because it is half of a day. Apply a time format such as h:mm to show 12:00, or multiply by 24 if you need the decimal number of hours, which is 12.
Can I convert a time written as text?
Often, yes. If A2 contains text that Excel recognizes as a time, =TIMEVALUE(A2) converts it to a numeric time value. The exact text format and regional settings affect whether Excel can interpret it.
Should I use TIME or divide by 24 for a duration?
Divide by 24 when converting a number of hours into a duration, especially if it may exceed 24 hours. TIME is designed to assemble clock-time components and wraps beyond a 24-hour day.
Related
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Examples: Exact, Approximate, and Wildcard MatchXLOOKUP’s match_mode argument controls whether Excel finds an exact value, a nearby value, or a wildcard pattern. For…
- COUNTIF Excel: How to Count Matches and Sum ValuesCOUNTIF in Excel counts cells that meet one condition; it does not add the values in those cells.…
- Excel Sort Numbers Low to High: Steps, Tips, and FixesTo sort numbers from low to high in Excel for Microsoft 365 on Windows, click a cell in…
- Count Each Unique Value in Excel with a FormulaTo count how often each value appears, put your source values in A2:A11, then enter =SORT(UNIQUE(FILTER(A2:A11,A2:A11<>""))) in D2…
- Excel MID Function From the Right: Formulas That WorkNo: Excel’s MID function always counts characters from left to right. To take a fixed number of characters…
- Excel Power Query Filter Rows: Practical Steps and ExamplesTo filter rows in Excel Power Query, open a query in the Power Query Editor, select the filter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.