If a real Excel date-and-time value is in A2, use =MOD(A2,1) to extract its time portion, then format the result as a time. The formula keeps the hours, minutes, seconds, and any fractional seconds while removing the date.
How Excel stores dates and times
Excel represents a date and time as a number. The whole-number portion represents the date, while the decimal portion represents the time as a fraction of a 24-hour day. For example, noon is one-half of a day, or 0.5; 6 a.m. is 0.25. A value such as March 8, 2025 at 2:35 p.m. therefore contains both a date number and a time fraction.
The MOD function returns the remainder after dividing a number by another number. With a divisor of 1, it returns the decimal portion of a positive date-time serial. If A2 contains March 8, 2025 at 2:35 p.m., =MOD(A2,1) returns approximately 0.6076389. That number is the time; it may initially look like a decimal because Excel has not yet been told to display it in a clock format.
This distinction between a value and its display matters throughout Excel. Formatting a cell to show only a time does not remove the date from the underlying value. Conversely, the MOD formula creates a separate time-only value that can be used in calculations, sorting, or charts without carrying along the original date.
Extract the time with MOD
For a single timestamp in A2, enter =MOD(A2,1) in another cell, such as B2. Fill or copy the formula down if the source timestamps continue in A3, A4, and so on. The formula adjusts its reference for each row, returning the time portion for each date-time value.
- Check that the source cell contains a real Excel date-time value, rather than text that merely looks like one.
- Enter the MOD formula in a blank cell beside the source data.
- Press Enter. If the result appears as a decimal, that is normal; the time is stored as a fraction of a day.
- Apply a time number format to the result cell. Choose a format that shows the detail you need, such as hours and minutes or hours, minutes, and seconds.
For the example March 8, 2025 at 2:35 p.m., the extracted time is 2:35 p.m. The date is not part of the new value. If the source includes seconds, MOD retains them even if the current display format hides them.
Format the result so it looks like a time
Select the result cell or range, then use Home > Number Format drop-down and choose a time format. For more control, open Format Cells with Ctrl+1 on Windows, select the Number tab, and choose Time or Custom. On Mac, Command+1 opens Format Cells. Excel for the web also offers number formats from the Home tab, though the available custom-format controls can differ from the desktop app.
Choose the display that matches your data. A format such as h:mm AM/PM displays 2:35 PM, while hh:mm displays 14:35. A format such as hh:mm:ss shows seconds as well. A leading zero in hh or mm makes single-digit hours or minutes display with two digits. These choices change how the value appears, not the value itself.
If you see a number such as 0.6076389 instead of a clock time, the extraction probably worked; apply a time format. If you see a date such as January 0, that often means Excel is displaying the fraction with a date format. Change the number format to Time or a suitable custom format.
Other formulas and when to use them
For positive Excel date-time values, subtracting the integer part is another way to get the time. With a datetime in A2, =A2-INT(A2) returns the fractional part. MOD is often easier to recognize as a remainder operation, while INT explicitly removes the whole-number portion. For ordinary modern timestamps, both approaches give the same time fraction.
If you need separate components rather than one time value, use Excel’s HOUR, MINUTE, and SECOND functions. With the date-time in A2, =HOUR(A2) returns 14 for 2:35 p.m.; with the same value in A2, =MINUTE(A2) returns 35; and with a timestamp of 2:35:20 p.m. in A2, =SECOND(A2) returns 20. Each formula returns a number, which is useful for calculations or labels but is not itself a complete clock time.
For example, if you want a time value that is explicitly reconstructed from its components, use =TIME(HOUR(A2),MINUTE(A2),SECOND(A2)) with the date-time in A2. This returns 2:35:20 p.m. for that example. It discards the date, but it also does not preserve fractional seconds beyond whole seconds. For a general extraction that should retain any fractional seconds, MOD is the better choice.
When the datetime is stored as text
MOD works on numeric date-time values, not on arbitrary text. A cell can look like 2025-03-08 14:35:20 but still be text, especially after importing a file or copying data from another system. A quick clue is that changing the cell’s number format has no effect, or that Excel aligns the content like text under the workbook’s current settings. Alignment is only a clue, not a definitive test.
For a text value in A2 consistently written in the ISO-style layout yyyy-mm-dd hh:mm:ss, extract the time characters and convert them with =TIMEVALUE(MID(A2,12,8)). For the text 2025-03-08 14:35:20, the formula returns a time fraction of approximately 0.60787037, which displays as 14:35:20 with an appropriate format. The formula assumes the date, space, and time always occupy those positions; it is not a general-purpose parser for mixed layouts.
TIMEVALUE recognizes time text according to Excel’s parsing and regional settings. If timestamps have inconsistent layouts, ambiguous date order, or unusual time-zone labels, fix or convert them during import instead of relying on fixed character positions. In Excel for Microsoft 365 on Windows, Data > From Text/CSV lets you inspect an import and set data types and locale options. Check the parsed results before using them in calculations.
Time of day is not elapsed duration
A time extracted with MOD represents a position within a day, from midnight up to (but not including) the next midnight. It is appropriate for questions such as what time a meeting began or when a transaction occurred. It is not the right display for an elapsed time that may exceed 24 hours.
For example, subtracting a start datetime from an end datetime gives an elapsed duration. If the result is 27 hours, a standard h:mm display may show 3:00 because the clock-hour format wraps after 24 hours. Apply a custom duration format such as [h]:mm or [h]:mm:ss to show the total hours. The square brackets tell Excel to display accumulated hours rather than wrap at each day boundary.
Be careful not to apply MOD to a duration when you need the total elapsed time: MOD(duration,1) deliberately removes complete days. A 27-hour duration would become a 3-hour time-of-day fraction. Also, subtracting two clock times alone can produce a negative result when an event crosses midnight. If the underlying values are full date-time stamps, subtracting them preserves the date difference; if you have times only, the calculation needs an explicit rule for overnight intervals.
Common problems and reliable fixes
- The formula returns an error: Confirm that the source is numeric and that the cell reference is correct. If the timestamp is text, convert it to a real date-time value or parse its time text as described above.
- The answer is a decimal: The number is the time fraction. Apply a time format rather than changing the extraction formula.
- The answer shows midnight or the wrong time: Check whether the source includes seconds, whether the displayed format hides them, and whether a text import interpreted the date or time incorrectly.
- The time seems to shift by hours: Excel date-time serials do not carry a time-zone designation. A timestamp imported from a system using UTC or another zone must be converted using the relevant offset and daylight-saving rules before it represents the desired local time.
- Results differ by a tiny amount: Excel stores numbers using binary floating-point arithmetic, so calculated values can have small representation differences. If a later comparison or calculation requires whole-second precision, round to the nearest second. With the date-time in A2,
=ROUND(MOD(A2,1)*86400,0)/86400returns the time rounded to the nearest second; format the result as a time to display it.
Excel workbooks can use different date systems, including the 1900 and 1904 systems. This mainly matters when serial date numbers are transferred between workbooks or applications without proper date conversion. For a date-time and extraction formula used together in the same workbook, MOD still isolates the fractional time portion.
Frequently Asked Questions
How do I extract only the time from a date and time in Excel? If the date-time is a real Excel value in A2, enter =MOD(A2,1) in another cell and format the result as a time. The date is removed while the time fraction remains.
Why does Excel show a decimal after I extract the time? Excel stores time as a fraction of one day, so a decimal is the underlying value. Apply a time number format to display it as a clock time.
Does MOD preserve seconds? Yes. MOD preserves the time fraction, including seconds and fractional seconds stored in the original value. The number format determines whether those details are visible.
How do I extract a time from a datetime stored as text? Convert the text to a real Excel date-time value or parse the time portion with TIMEVALUE. A formula based on MID can work when every text timestamp has the same known layout, but it may fail when formats vary.
How do I show more than 24 hours? For an elapsed duration, use a custom format such as [h]:mm or [h]:mm:ss. Do not use MOD to extract a time of day if you need to keep complete days in the duration.
Will formatting the original cell remove its date? No. Formatting only changes what is displayed. Use MOD in a separate cell when you need a separate time-only value for subsequent calculations.
Related
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Create Mailing Labels in WordTo create mailing labels in Word, open the Mailings tab, choose Start Mail Merge > Labels, select the…
- Excel Solver: Add Decision Variables and Set ConstraintsYes. To add variables in Excel Solver, put each decision variable in a worksheet cell and include those…
- Can You Undo Remove Duplicates in Excel?Yes. In Excel for Microsoft 365 on Windows, immediately press Ctrl+Z after using Remove Duplicates, or click Undo…
- Excel Group By and Remove Duplicates: How to ChooseTo delete repeated records in Excel, select your data and choose Data > Remove Duplicates, then select the…
- Excel Track Changes: How to Turn It OnTo turn on the traditional Track Changes mode in Excel for Microsoft 365 on Windows, open the workbook,…
- Excel Middle Years Academy: What Families Should KnowExcel Middle Years Academy appears to be the name of a school or education provider, not a Microsoft…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.