Explanation
In this example, the goal is to extract the time portion of a date that contains time (also called a “datetime”). Since dates in Excel are serial numbers and times are fractional values of a day, the task is to extract the decimal portion of the serial number. This is easy to do with the MOD function, and other methods mentioned below. Note that if you are comparing extracted times to other time values, there is a subtle floating point precision issue you should be aware of.
- How Excel handles dates and times
- Using MOD to extract time
- Other methods to extract time
- Floating point precision issue
- Workaround: use TIME or ROUND
How Excel handles dates and times
Excel handles dates and times using a system in which dates are serial numbers and times are fractional values of a day. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date (June 1, 2000) and .5 is the time (12:00 PM). Since 12:00 PM is exactly halfway through a day, Excel represents it as 0.5. Likewise, 6:00 AM is 0.25 (one quarter of a day) and 6:00 PM is 0.75 (three quarters of a day). In other words, the time value in a “datetime” is the decimal portion of the number.
Using MOD to extract time
The MOD function returns the remainder from division. The first argument is the number and the second is the divisor. Here are a few examples:
=MOD(5,2) // returns 1
=MOD(7,5) // returns 2
If you use MOD with a divisor of 1, the result will be the decimal part of the number, if any, because every whole number can be evenly divided by itself. For example:
=MOD(3.5,1) // returns 0.5
=MOD(3.125,1) // returns 0.125
In short, =MOD(number,1) returns just the fractional part of a number, discarding the integer portion, so it’s a convenient way to extract time from a date.
If you use this formula to strip the time from a date + time, you’ll need to apply a suitable number format to display as time.
Other methods to extract time
What’s nice about MOD is that it’s a simple one-function solution. However, there are other ways to extract time from a datetime. The most common alternatives subtract the integer (date) portion from the original value using the INT function or the TRUNC function :
=B5-INT(B5)
=B5-TRUNC(B5)
Both formulas work the same way: they calculate the date portion and subtract it from the datetime, leaving just the time. Another option is to rebuild the time from its components using TIME , HOUR , MINUTE , and SECOND :
=TIME(HOUR(B5),MINUTE(B5),SECOND(B5))
This approach extracts each time component as an integer, then reassembles them into a time value. This is a more verbose formula, but it has the advantage of avoiding the floating-point precision issue described below.
Floating point precision issue
The MOD formula works well for displaying extracted times, but there’s a subtle gotcha you should know about. Because of the way computers handle decimal numbers (known as floating point arithmetic ), the result from MOD may not be exactly equal to the same time created with the TIME function . This can cause problems when comparing extracted times.
For example, say you have a datetime like October 20, 2024, 4:00 PM in cell A1. If you extract the time with MOD and compare it to 4:00 PM created with TIME, you might expect them to be equal:
=MOD(A1,1)=TIME(16,0,0) // may return FALSE!
The result may be FALSE because MOD returns something like 0.6666666666642413 instead of 0.666666666666667 (the value TIME returns for 4:00 PM). The difference is tiny (invisible when formatted as time) but enough to break equality checks. You can see examples of these very slightly different values for 1:00 PM and 4:00 PM in cells E15 and E16, respectively:
This issue isn’t specific to MOD. Any formula that uses subtraction to isolate the time portion will have the same problem, including formulas based on INT, TRUNC, and DATE :
=A1-INT(A1) // same issue
=A1-TRUNC(A1) // same issue
=A1-DATE(YEAR(A1),MONTH(A1),DAY(A1)) // same issue
Workaround: use TIME or ROUND
If you need to compare extracted times to other time values, here are two reliable solutions. The first option is to rebuild the time with TIME. The formula extracts the hour, minute, and second with HOUR, MINUTE, and SECOND, then rebuilds the time value using TIME:
=TIME(HOUR(B5),MINUTE(B5),SECOND(B5))
Because TIME builds the time from scratch using integer values, it avoids the precision issue entirely.
Another good option is to round before comparing values with the ROUND function . Rounding to about 10 decimal places is enough:
=ROUND(MOD(B5,1),10)=ROUND(TIME(16,0,0),10) // reliable comparison
Either approach will give you consistent results when comparing times.
Explanation
In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the “Months” column. The “Replaced” column shows the date equipment was replaced. The “Expires” column shows the date it will need to be replaced again.
All data is in an Excel Table named data in the range B5:E16 and the dates to check are in the “Expires” column. In addition, the current date is in the named range date (H2) and the number of days to use when deciding if a date is expiring soon is in the named range days (J2). Dates already expired are highlighted in yellow with conditional formatting. The named ranges are for convenience only, to make the formula easier to read and write.
You can use this same approach to filter on any data that has an upcoming date. i.e. retirements, events, renewals, etc.
Note: In the example shown, the current date is 7-July-2022, provided by the TODAY function. As time goes by, the current date will move forward, and more dates will be expired or expiring soon. To see the worksheet in its original state, you can hardcode the date 7-July-2022 into cell H2.
The core logic
In the example shown, the formula in G5 is:
=SORT(FILTER(data,data[Expires]-date<=days),4)
Working from the inside out, the core logic in this formula first works out the difference between the value in date (H2) and all dates in column E:
data[Expires]-date<=days)
Because Excel dates are just large serial numbers , we simply subtract the value in date from each date in the Expires column. When the expiration date is in the past, the result is a negative number. When the expiration date is in the future, the result date is a positive number. In the example shown:
data[Expires]-date
returns the following array :
{-36;33;14;293;331;3;248;10;34;-3;17;35}
Each number represents the number of days until expiration. Again, negative numbers are dates already expired . When this array is compared to days (J2) which is 15:
={-36;33;14;293;331;3;248;10;34;-3;17;35}<=15
the result is an array of TRUE and FALSE values like this:
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}
Each TRUE value represents a date that has already expired or will expire in the next 15 days. We can use this same logic in the FILTER function to select dates expiring soon.
FILTER function
The next step is to implement the logic above inside the FILTER function . To filter the dates to show those expiring within 15 days, we embed the expression explained above inside FILTER like this:
FILTER(data,data[Expires]-date<=days)
The array argument is the Excel Table data , and the include argument is the logical expression explained above. In this configuration, FILTER returns the five rows from the table where the Expires date has already occurred or will occur in the next 15 days. Note that this result is based on the current date in cell H2, which is 7-July-2022.
SORT function
The final step in the problem is to sort the data by expiration date, so that dates already expired are listed first, followed by dates expiring soon in the order they will expire. This can be done by nesting the FILTER formula inside the SORT function like this:
=SORT(FILTER(data,data[Expires]-date<=days),4)
Here, the array provided to SORT is the result returned by FILTER, and sort_index is given as 4, since the “Expires” date is in the fourth column of the table.
Current date
In the worksheet shown, the current date is provided by the TODAY function :
=TODAY()
TODAY takes no arguments, and will return the current date on an ongoing basis. This is often what you want in real life, where the data being tracked is always changing. However, it can be confusing in the case of this example, because the worksheet in the future will not look the same as the worksheet looks today. To make the worksheet look like it did on July 7, 2022, just hardcode that date into cell H2.
Conditional formatting
In the example shown, conditional formatting is used to highlight dates that have already expired. The formula used to apply the rule is based on the AND function :
=AND($J5<>"",$J5<=date)
In this formula, we check two conditions: (1) the date in J5 is not empty and (2) the date in J5 is less than or equal to (<=) date (H2), which holds the current date. This is an example of a mixed reference – the column is locked in order to highlight the entire row .
Expires date
The date in the “Expires” column is calculated with the EDATE function . The formula in E5 is:
=EDATE(C5,D5)
Starting with the “Replaced” date, EDATE returns moves forward the number of months given in “Months” and returns the resulting date.
