Extract time from a date and time

To extract the time from a date that contains time (sometimes called a "datetime"), you can use a formula based on the MOD function. In the example shown, the formula in D5 is:

=MOD(B5,1)

which returns the time portion of the value in B5, 1:00 PM.

Explanation

Excel handles dates and times using a system in which dates are serial numbers and times are fractional values. 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). In other words, the time value in a "datetime" is the decimal.

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.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.

Note: 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.