How to Compare Times Without Including the Date in Excel
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.

- 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.
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.
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.
Click on the cell where you want the comparison result to appear (for example, F7).
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.
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.

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. Open your file in WPS: Launch WPS Office and open your spreadsheet containing the datetime data.
- 2. Input the formula: Select your target cell and enter the MOD and IF formula just as you would in Excel.
- 3. Drag to fill: Use the fill handle to drag the formula across your desired range to instantly compare all times.

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.




