How to extract the time from a date and time in Excel

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

In Google Sheets

The same formula works in Google Sheets: =MOD(A2,1). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Common mistakes

• The result is a fraction like 0.34 until you format it as a time.
• For text timestamps, TIMEVALUE returns the time part: =TIMEVALUE(A2).

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Related answers

• How to combine a date and a time into one cell in ExcelAdd them: =B2+C2. A date is a whole number and a time is a fraction, so the sum holds both.
• How to convert decimal hours to hours and minutes in ExcelDivide by 24 and format the cell as h:mm: =B2/24 turns 7.75 into 7:45.
• How to convert military time to standard time in ExcelSplit the number into hours and minutes: =INT(A2/100)/24+MOD(A2,100)/1440, then format as h:mm AM/PM.
• How to convert minutes to hours and minutes in ExcelDivide by 1,440, the minutes in a day, and format as h:mm: =B2/1440 turns 135 into 2:15.
• How to convert seconds to minutes and seconds (mm:ss) in ExcelDivide by 86,400 and format as mm:ss: =B2/86400 shows 95 seconds as 01:35.
• How to show a duration as days, hours and minutes in ExcelTake the whole days with INT and format the rest with TEXT: =INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""").

Functions used: MOD · INT · TEXT

Lessons: DATE, YEAR, MONTH & TEXT (Pro)