How to Extract Time from a Date and Time Stamp in Microsoft Excel

Enterprise data logs, e-commerce transaction exports, and employee badge swipes frequently export dates and times bundled together in a single serial timestamp (such as 2026-10-27 14:45:30). When building operational shift dashboards or analysing peak order hours, having the date and time merged in one cell makes grouping, charting, and average hour calculations cumbersome. Understanding how Microsoft Excel represents dates and times mathematically enables you to isolate the pure time component cleanly without altering underlying serial accuracy.

Understanding How Excel Stores Dates and Times

In Microsoft Excel’s internal calculation architecture, date-time values are represented as floating-point serial numbers:

• The integer portion of the serial number represents the date (the count of days elapsed since 1 January 1900). For example, day 45600 corresponds to a specific calendar date.
• The decimal fraction represents the time of day as a portion of 24 hours. For example, 0.5 represents exactly 12:00 PM (midday), 0.25 represents 6:00 AM, and 0.75 represents 6:00 PM.

Because time is simply the fractional remainder of a date serial number, extracting time involves stripping away the whole integer date.

Method 1: Extracting Time with the MOD Function (Recommended)

The MOD function calculates the mathematical remainder after dividing by a divisor. Dividing any number by 1 returns its exact decimal fraction:

1. Select an empty adjacent column cell where the extracted time should appear (e.g. B2).
2. Enter the following formula referencing your combined timestamp in cell A2:

   =MOD(A2, 1)

3. Press Enter.
4. The cell may initially output a raw decimal (such as 0.6149).
5. With cell B2 selected, switch to the Home tab on the ribbon.
6. In the Number format drop-down, select Time (or press Ctrl + Shift + @ / Ctrl + Shift + 2 on Windows).

The formula instantly displays the correct clock time (e.g. 2:45:30 PM) completely stripped of its calendar date.

Method 2: Extracting Time Using INT Subtraction

An alternative mathematical approach uses the INT (integer) function to subtract the date portion directly:

=A2 - INT(A2)

The INT(A2) function rounds the serial down to the nearest whole integer (the date). Subtracting the date leaves only the fractional time of day. Format the resulting cell as Time to view standard clock hours.

Converting Extracted Time into Exact Hours, Minutes, or Seconds

If you need numerical units for payroll rate calculations rather than a display timestamp:

• Convert to Decimal Hours: Multiply the extracted fraction by 24:

  =MOD(A2, 1) * 24

  Format as General or Number. 2:30 PM cleanly evaluates to 14.5 hours.
• Extract Hour Component: Use the native HOUR function to return an integer from 0 to 23:

  =HOUR(A2)

• Extract Minute Component: Use the native MINUTE function:

  =MINUTE(A2)