Extract Time or Hour Only from a Date and Time in Excel

This post will guide you how to extract time from a data and time value with a formula in Excel. How do I extract time from a date-time number in Excel 2013/2016. How to extract hour only from the date time format in Excel.

Extract Time from Date and Time


If you want to extract time from a date and time in Excel, you can use a formula based on the TIME function, The HOUR function, the MINUTE function, and the SECOND function. For example, you want to get time value from a date and time value in Cell B1, you can write down the below formula.

=TIME(HOUR(B1),MINUTE(B1),SECOND(B1))

#1 Type this formula into a blank cell C1 and press Enter key.

extract time and hour1

#2 Then you need to right click on the Cell C1, and select Format Cell from the pop-up menu list. The Format Cells dialog will open.

extract time and hour2

#3 switch to Number tab, and click Custom from the Category list. And select one type as: h:mm:ss. Click OK button.

extract time and hour3

#4 let’s see the result.

extract time and hour4

 

Extract Hour only from Date and Time


If you want to extract hour only, you just need to use the HOUR function. Like this:

=HOUR(B1)

extract time and hour5

If you want to extract minute only from a date and time value, you just need to use the MINUTE function. Like this:

=MINUTE(B1)

extract time and hour6

If you want to extract second only from a date and time value, you just need to use the SECOND function. Like this:

=SECOND(B1)

extract time and hour7

Related Functions


  • Excel TIME function
    The Excel TIME function returns a date in serial number format for a particular time (the hour, minute and second).The syntax of the TIME function is as below:= TIME(hour, minute, second)…
  • Excel HOUR Function
    The Excel HOUR function returns the hour of a time value. Or returns an integer value that represent the hour component of a given time. The syntax of the HOUR function is as below:=HOUR (serial_number) …
  • Excel MINUTE Function
    The Excel MINUTE function returns the minutes of a time value. And the minutes is an integer number from 0 to 59. The syntax of the MINUTE function is as below:=MINUTE (serial_number)…
  • Excel SECOND Function
    The Excel SECOND function returns the seconds of a time value. Or returns an integer value that represent the second component of a given excel time. And the return value is between 0-59.The syntax of the SECOND function is as below:=SECOND (serial_number)…

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar