Formula Errors

How to Fix Excel INT or TRUNC Date and Time Lookup Errors

Rana Garcia Oct 10, 2026 870 views

Question details

The user needs to fix lookup formula errors that occur when using INT or TRUNC to evaluate cells containing both date and time values.

Fix Excel INT or TRUNC Date and Time Lookup Errors

Product: Excel
Device & OS: not provided
Scenario: Performing week-label or date-range lookups using VLOOKUP or XLOOKUP against data ranges sourced from combined date-time strings.
Observed behavior: Lookups return FALSE or unexpected results, and formula evaluations may behave differently across different computers.

Before you start

Before modifying your formulas, verify that the first column of your lookup table is sorted in ascending order, which is strictly required for approximate match lookups.

Solution 1Recommended

Normalize Date-Time Values and Apply Approximate Match VLOOKUP

Remove the hidden time component from your source data using the INT function to ensure reliable date lookups across all devices.

In Excel, dates are stored as whole numbers and times as decimal fractions. A cell containing both (e.g., 1/1/2023 12:00 PM) will fail to match a plain date (1/1/2023) in a lookup table. Normalizing the value removes the decimal fraction.

Extract the Date

Select a blank cell adjacent to your source date-time data (e.g., cell AC2). Enter the formula =INT(A2) to strip away the time fraction and retain only the whole date number.

Format Cell as Date

Click on the cell with the new formula, navigate to the Home tab on the Excel ribbon, click the Number Format dropdown menu, and choose 'Short Date'.

Implement VLOOKUP

Construct an approximate match lookup referencing your sorted table. Enter the formula =VLOOKUP(AC2,math!$A$1:$C$7,3,TRUE) to retrieve the week label or corresponding value.

Verify Regional Settings

If errors persist on different computers, check the OS Control Panel for 'Region and Language' settings to ensure both computers use the same date format (e.g., MM/DD/YYYY).

Normalize Date-Time Values and Apply Approximate Match VLOOKUP

Data Normalization Complete: Using TRUE in your VLOOKUP allows Excel to evaluate boundary dates correctly, provided the source lookup range is sorted from oldest to newest.

Efficient Spreadsheets

Easily Manage Date and Time Formulas in WPS Spreadsheet

WPS Spreadsheet offers powerful and precise formula calculation for dates and times, ensuring your lookups remain accurate across different devices without frustrating formatting errors.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date-time values and lookup tables.
2. Normalize the data: Select a target cell and type =INT(A2) to extract the pure date portion from your date-time values.
3. Format the cells: Highlight the formula cells, right-click and select 'Format Cells', then choose 'Date' under the Number tab to visually verify the change.
4. Insert VLOOKUP: Type your =VLOOKUP() formula using TRUE for the range lookup argument to get an accurate approximate match.

Fully compatible with Microsoft Excel formulas including VLOOKUP, INT, and TRUNCConsistent cross-platform date and time formattingLightweight architecture for fast processing of large lookup tablesIntuitive cell formatting panel to quickly normalize data types

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return FALSE when looking up dates?

This commonly happens when the source cell contains hidden time values (e.g., 14:00 as a decimal fraction) while the lookup table contains only whole-number dates. The slight difference in numeric value causes the exact match to fail.

What is the difference between INT and TRUNC when working with dates?

For positive numbers (dates after January 1, 1900), both INT and TRUNC remove the decimal portion (the time) and return the same date result. INT rounds down to the nearest integer, whereas TRUNC simply cuts off the decimals.

Why do my formula results differ when I open the file on another computer?

Formula results involving dates can vary across computers if they possess different regional settings in the operating system (like MM/DD/YYYY versus DD/MM/YYYY) or if one Excel installation defaults to the 1904 date system instead of the standard 1900 date system.

Can I use XLOOKUP for date ranges instead of VLOOKUP?

Yes, XLOOKUP is highly versatile but defaults to an exact match. To evaluate against date boundaries (like week-labels), you must configure its match mode argument to -1 (exact match or next smaller item) and ensure your source date is normalized.

Excel & Data Problems