Function Problems

How to Extract Time from Date and Time in Excel for Lookups

Amos Gikunda Oct 21, 2026 868 views

Question details

The user needs to isolate the time portion from a combined date-and-time cell in order to perform lookups based exclusively on the time of day.

How to Extract Time from Excel Date and Time Values

Product: Excel
Device & OS: not provided
Scenario: Matching spreadsheet records by time of day or checking if a timestamp falls within a specific daily time range, regardless of the calendar date.
Observed behavior: Excel date-and-time values combine an integer representing the date and a fraction representing the time, preventing direct time-of-day matching without first isolating the fractional part.

Before you start

Verify that your combined date and time values are recognized by Excel as true numerical values and not formatted as plain text, as extraction formulas rely on mathematical calculations.

Solution 1Recommended

Extract Time Using the MOD Function

This is the most efficient method to separate time. Since Excel stores dates as whole numbers and times as decimal fractions, the MOD function cleanly extracts only the fractional time portion.

The MOD function calculates the remainder after division. By dividing the date-time serial number by 1, it strips away the whole number (the date) and leaves only the decimal (the time).

Enter the MOD formula

Select a blank cell next to your original data (e.g., C3) and type the formula =MOD(B3,1), assuming B3 contains the original date and time.

Apply the formula

Press Enter. You will likely see a decimal number, which represents the time fraction in Excel's internal system.

Format as Time

Right-click the result cell, select 'Format Cells', navigate to the 'Number' tab, click 'Time', and choose your preferred time display format. Click OK.

Perform your lookup

Use this newly extracted time column as the lookup value in your VLOOKUP or XLOOKUP formula to match against your target time range.

Lookup Compatibility: Ensure your lookup reference table is also properly formatted as Time so the exact decimal values match accurately during the lookup.

Solution 2

Extract Time Using the TIME Function

This method reconstructs the time by extracting the specific hour, minute, and second components from the original cell.

Powerful Data Management

Easily Process Date and Time Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced data extraction methods, including MOD and TIME functions. It offers a fast, reliable environment for managing complex datasets and running precise lookups without compatibility issues.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your combined date and time values.
2. Apply the extraction formula: Select an empty cell, type =MOD(B3, 1), and press Enter to extract the raw time fraction.
3. Format the values correctly: Right-click the cell, choose 'Format Cells', and select the Time category to make the data readable.
4. Execute your lookup: Use the VLOOKUP function referencing the newly extracted time column to effortlessly match records by time of day.

Fully compatible with Microsoft Excel formats (.xlsx) and formulasFree and lightweight alternative for fast spreadsheet processingIntuitive interface for formatting and analyzing time-sensitive dataBuilt-in support for advanced lookup functions like VLOOKUP and XLOOKUP

microsoft office alternative - wps office

Frequently Asked Questions

Why does my extracted time show as a strange decimal number like 0.75?

Spreadsheet software stores time as a fractional portion of a 24-hour day. For example, 0.75 represents 18:00 (6:00 PM) because it is three-quarters of a day. To fix the display, simply right-click the cell, select Format Cells, and apply a Time format.

Can I use VLOOKUP directly on date-and-time cells without extracting the time?

No, VLOOKUP and similar functions attempt to match the entire exact value stored in the cell, which includes the hidden date integer. You must separate the time portion into its own column first to perform a time-only lookup.

What if I want to extract the date instead of the time?

To extract only the date, you can use the INT function (e.g., =INT(B3)). Because dates are stored as whole numbers, INT rounds the value down to the nearest integer, effectively dropping the fractional time portion.

Will these time extraction formulas work in WPS Office?

Yes, WPS Spreadsheet is fully compatible with Excel's date and time functions. You can use MOD, TIME, HOUR, MINUTE, and SECOND seamlessly in WPS Office exactly as you would in Excel.

Excel & Data Problems