Calculation Issues

How to Compare Times Without Including the Date in Excel

Muhammad TalhaMuhammad Talha Nov 11, 2026 870 views

Question details

The user needs to compare time values extracted from cells containing full date-and-time data to check if they fall within a specific time range.

How to Compare Times Without Including the Date in Excel
Product
Excel
Device & OS
not provided
Scenario
Identifying whether a specific time falls between a start and end time across multiple dates, without altering the original date-and-time source cells.
Observed behavior
The user wants to successfully evaluate time ranges using a formula that ignores the date portion of the datetime values.
Before you start

Ensure your source cells are formatted as valid date-and-time values so that Excel can correctly process them mathematically rather than treating them as text.

Solution 1Recommended

Use the MOD Function to Extract and Compare Times

The MOD function is the most efficient way to strip the date integer from a datetime value, leaving only the decimal time portion for comparison.

In Excel, dates are stored as whole numbers (representing days) and times as fractional decimals. By using MOD(value, 1), you divide the datetime value by 1 and keep only the remainder, which perfectly isolates the time portion.

1
Select the target cell

Click on the cell where you want the comparison result to appear (for example, F7).

2
Enter the MOD comparison formula

Type the formula =IF((F$6<MOD($C7,1))*(F$6>=MOD($B7,1)),"x",""). In this example, F6 is the time to check, B7 is the start datetime, and C7 is the end datetime.

3
Apply and copy the formula

Press Enter to apply the formula, then click and drag the fill handle across and down to copy it to other cells in your grid.

Use the MOD Function to Extract and Compare Times
Formula Logic: The formula multiplies two logical tests (less than end time, greater than or equal to start time). If both are true, it returns 'x'; otherwise, it returns a blank.
Free Excel Alternative

Easily Compare Dates and Times with WPS Spreadsheet

WPS Office provides a powerful, free spreadsheet tool that fully supports advanced Excel functions like MOD and IF. You can seamlessly handle complex datetime calculations and data analysis with a familiar interface.

  1. 1. Open your file in WPS: Launch WPS Office and open your spreadsheet containing the datetime data.
  2. 2. Input the formula: Select your target cell and enter the MOD and IF formula just as you would in Excel.
  3. 3. Drag to fill: Use the fill handle to drag the formula across your desired range to instantly compare all times.
Fully compatible with Microsoft Excel (.xlsx) formatsSupports all standard date and time functions for complex calculationsLightweight and fast for processing large datasetsFree to use with a familiar, user-friendly interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does MOD(value, 1) extract the time from a date?

In spreadsheet software, dates are stored as whole numbers (representing days since Jan 1, 1900) and times are stored as decimals (fractions of a day). The MOD function returns the remainder after division. Dividing by 1 removes the whole number (the date) and leaves only the decimal (the time).

Can I use the TIME function instead of MOD to compare times?

While you can use functions like HOUR, MINUTE, and SECOND to reconstruct a time, using MOD(value, 1) is much shorter and more efficient for extracting the exact time value for direct mathematical comparisons.

What if my time comparison crosses midnight?

If your start time is later than your end time (indicating an overnight shift), the standard comparison formula will fail. You will need to adjust the formula to account for the next day, typically by adding 1 to the end time or using an OR logic condition.

Why is my formula returning an error instead of a blank or 'x'?

This usually happens if the source cells are formatted as text rather than actual date-and-time values. Use the VALUE function or reformat the cells to ensure they are recognized as numbers by the spreadsheet.