Exceljet

Quick, clean, and to the point

Extract time from a date and time

Excel formula: Extract time from a date and time
Generic formula 
=MOD(A1,1)
Summary 

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.