In Excel, a time value is a number representing a fraction of a 24-hour day: for example, 6:00 AM is 0.25 and noon is 0.5. Excel displays that number as a clock time when the cell has a time format, so understanding the stored value helps explain how to enter, calculate, and troubleshoot times.
What Excel means by a time value
A time value is not merely text that looks like a clock reading. When Excel recognizes an entry such as 9:30 AM as a time, it stores a numeric value that can be used in arithmetic. The displayed appearance and the underlying value are separate: the cell may show “9:30 AM,” while its stored value is 0.3958333, or 9.5 hours divided by 24.
This distinction matters because the same number can be displayed in different ways. A value of 0.5 might appear as 12:00 PM with a time format, as 50% with a percentage format, or as 0.5 with a general or numeric format. Changing the format changes the display, not the stored value. By contrast, replacing the contents with text changes what Excel can calculate.
How Excel stores times
Excel represents a time as a fraction of one day. Midnight is the start of the day, or 0; one full day is 1. Common examples include:
- 6:00 AM is 6 ÷ 24, or 0.25.
- 12:00 PM is 12 ÷ 24, or 0.5.
- 6:00 PM is 18 ÷ 24, or 0.75.
- 6:00 AM plus 30 minutes is 6.5 ÷ 24, or about 0.270833.
Minutes and seconds are fractions too: one minute is 1/1,440 of a day, and one second is 1/86,400. A date-time value combines a whole-number day count with a fractional time. For example, a value representing a particular date at noon has the date’s serial number plus 0.5. This shared numeric system is why subtracting two recognized date-time values can return elapsed time.
Excel’s calculations use binary floating-point numbers, so some decimal fractions cannot be represented with perfect precision internally. Usually this is invisible because Excel formats the result to a sensible number of digits. If a calculation appears to have a tiny residual, format the result appropriately or use rounding when the calculation requires a defined precision.
Entering and displaying a time
Enter a time in a form Excel recognizes for your regional settings, such as 9:30 AM or 21:30. Excel’s interpretation of separators, AM/PM conventions, and dates can depend on the Windows or Office language and regional settings. If an entry stays left-aligned as text, does not respond to number formatting, or cannot be used in time arithmetic, Excel may not have recognized it as a time.
To change how a recognized value appears in Excel for Microsoft 365 on Windows, select the cell and use Home > Number Format to choose a time style. For more control, press Ctrl+1 to open Format Cells, then select Number and choose Time or Custom. On Mac, Command+1 opens Format Cells; the Home tab’s number-format controls are also available. Excel for the web offers number-format controls on the Home tab, though its dialog and available custom-format options can differ from the desktop app.
Formatting does not convert text into a time. If imported data is text, first convert it to real numeric time values using an appropriate import or conversion method; the right method depends on the text’s structure and regional conventions. Avoid assuming that a value is valid just because it visually resembles a clock reading.
Calculating elapsed time
When both cells contain recognized times on the same day, subtract the start from the end. With a start time of 6:00 AM in A2 and an end time of 12:00 PM in B2, enter this in C2:
With the start time in A2 and the end time in B2, =B2-A2 returns 0.25, which represents six hours. Format C2 as a time to display 6:00, or as a duration format to make clear that it is elapsed time.
A shift that crosses midnight needs different treatment if the cells contain only clock times. With a start time of 10:00 PM in A2 and an end time of 6:00 AM in B2 on the following morning, =B2-A2 is negative. If the intended answer is the forward elapsed time within one 24-hour cycle, use this in C2:
With the start time in A2 and the end time in B2, =MOD(B2-A2,1) returns 0.3333333, equivalent to eight hours. Format the result as a time or duration to display 8:00.
There is an important limitation: MOD wraps the answer into a single day. If an event can last more than 24 hours, or you need to represent its actual dates, enter full date-time values for both start and end, then subtract them. For example, a start on Monday at 8:00 AM and an end on Tuesday at 10:00 AM should produce 26 hours, not two hours. A duration format such as [h]:mm displays the full result.
Durations and totals over 24 hours
A clock time and a duration are related but not identical in how they should be displayed. A clock reading normally wraps around at midnight; an accumulated duration should keep counting. The standard h:mm format can display 26 hours as 2:00 because it shows the hour within a day. For total elapsed hours, use the custom format [h]:mm. The square brackets tell Excel to show cumulative hours rather than reset the hour count after 24.
For example, suppose B2:B4 contain durations of 7:30, 8:15, and 9:00. With those durations in B2:B4, enter =SUM(B2:B4) in B5. The result is 1.03125 as a stored number of days, or 24 hours and 45 minutes. Apply the custom format [h]:mm to B5 to see 24:45. Without the brackets, a normal time display may show 0:45, which can look like Excel lost a day even though the sum is correct.
The same principle applies to durations stored as days and fractions of days. Adding times does not automatically make the result a clock reading; choose a display format that matches the question. Use a standard time format for “what time is it?” and a bracketed hour format for “how long altogether?”
Converting time values to decimal hours
Because one day equals 24 hours, multiply a time or duration value by 24 to express it as decimal hours. With a duration of 2:30 in A2, enter =A2*24 in B2. The result is 2.5 hours, since 2 hours 30 minutes equals 2.5 hours. If B2 displays a clock time instead, change its number format to General or Number.
To convert decimal hours back to an Excel time value, divide by 24. With 2.5 in A2, enter =A2/24 in B2. The result is 0.1041667 days, which displays as 2:30 when formatted as h:mm. This conversion is useful for interpreting decimal-hour data, but check what the source means: a number such as 2.5 could instead mean two and a half days if the data is defined in days.
Excel can also construct a time from hour, minute, and second components. With the hour 14 in A2, minute 30 in B2, and second 0 in C2, enter =TIME(A2,B2,C2) in D2. The result displays as 2:30 PM with an appropriate time format. TIME normalizes out-of-range components, so it can roll excess minutes or hours into the next unit; it does not preserve a multi-day duration as an hour count.
Date systems and workbook compatibility
Excel workbooks can use either the 1900 date system or the 1904 date system. The setting determines the serial number used for a given date; time fractions still represent fractions of a day. The 1900 system is commonly used in Windows workbooks, while the 1904 system is associated with some older Mac workbooks. Current workbooks can use either, so check the workbook’s setting rather than assuming it from the computer.
In the 1900 date system, Excel preserves a historical compatibility behavior involving the nonexistent date February 29, 1900. This can affect calculations involving dates near the start of that system, but ordinary modern dates and time-of-day fractions are generally unaffected. When transferring date values between workbooks using different date systems, the displayed date can shift unless the values are handled correctly.
On Windows, inspect or change the setting under File > Options > Advanced, in the section titled When calculating this workbook. On Mac, the setting is found in Excel > Preferences or Settings, under Calculation, depending on the release. Changing a workbook’s date system can alter how existing dates are interpreted, so do not switch it casually in a workbook that already contains dates. If a workbook’s dates appear offset after being shared, compare the date-system setting on both files.
Common time-value problems
- The result shows a decimal. The value may be correct but formatted as General or Number. Choose a time format for a clock reading or [h]:mm for a cumulative duration.
- The total appears to reset after 24 hours. Use the custom format [h]:mm rather than h:mm. This changes only the display, not the underlying total.
- Subtraction gives a negative result across midnight. Use MOD for a duration under 24 hours when the cells contain only times, or store full date-time values when the dates matter.
- Excel does not calculate with the entry. The cell may contain text, or the formula cell may itself be formatted as Text. Confirm that the input is recognized as numeric and that the formula is not being displayed literally.
- A time looks one hour out. Check whether the source data includes dates, whether the values were imported under different regional settings, and whether the calculation uses actual date-time values rather than time-only entries. A time-only value does not contain a time zone or daylight-saving offset.
For imported lists, also watch for inconsistent formats within a column: some rows may be true Excel times and others text. A consistent display alone is not proof that every row has the same underlying data type. Testing arithmetic on representative rows can help reveal the difference.
Frequently Asked Questions
What is 0.5 as an Excel time?
It is half of a day, so it represents 12 hours. With a time format it displays as 12:00 PM; with a General format it may display as 0.5.
How do I show more than 24 hours in Excel?
Apply the custom number format [h]:mm to the duration cell. The brackets keep the total hour count from restarting at zero after each day.
Why does Excel show 0.25 instead of 6:00 AM?
The value is likely stored correctly as a fraction of a day, but the cell is using a general or numeric format. Select a time format to display it as a clock reading.
Can Excel calculate a shift that ends the next day?
Yes. Use full date-time entries and subtract the start from the end for an unambiguous result. If you have only two time-of-day values and know the shift lasts less than 24 hours, MOD can calculate the forward duration across midnight.
Does an Excel time include a time zone?
No. An Excel time value is a numeric fraction of a day and carries no built-in time-zone or daylight-saving information. Include separate data or logic if a task requires those distinctions.
Related
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Block a Sender in Outlook Mail: Steps, Limits and TipsTo block a sender in Outlook, select a message from that person, open the message actions or Junk…
- Google Sheets Data Validation Dropdown: Setup and TipsTo create a Google Sheets data validation dropdown, select the cells, choose Data > Data validation > Add…
- Freeze Panes in Excel: Lock the First Column and Header RowTo keep column A visible while you scroll in Excel for Microsoft 365 on Windows, select View >…
- Excel Blue Line Print Area: What It MeansIn Excel, blue lines in Page Break Preview mark page breaks—the boundaries between printed pages—not necessarily the edge…
- Create Your Own PowerPoint Template for Free: A GuideYes—you can design a PowerPoint template without paying for a template file: use PowerPoint for the web with…
- Excel Shortcut to the End of Data: Keys and LimitsIn Excel for Microsoft 365 on Windows, press Ctrl+Down Arrow to jump to the edge of the current…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.