How to Extract Date or Time from Datetime in Excel

Are you tired of trying to extract date or time from datetime values in Excel? This post will show you how it’s done. Whether you need to separate the date component, the time component, or both, this guide has you covered. You’ll learn how to use formulas and functions to easily extract date or time from datetime values 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.

1. Extract Date from Datetime

To extract or get the date from a datetime value in one cell in Excel, you can use the following formula:

=TEXT(datetime,"MM/DD/YYYY").

You need to replace “datetime” with the cell reference that contains the datetime value. This formula converts the datetime value to text in the desired date format (MM/DD/YYYY in this case).

Extract Date from Datetime

The result will be the date part of the datetime value, displayed as text string. If you need to use the result in further calculations, you can convert the result back to a date value by using the “DATEVALUE” function.

=DATEVALUE(B2)
Extract Date from Datetime

You can also use the “=INT()” function to convert a date and time to a date:

=INT(A2)

Extract Date from Datetime int function

This function will return the date portion of the date and time value as a serial number, which can be formatted as a date using the “Number Format” option in the “Home” tab of the Excel ribbon.

2. Extract Date from Text String

To extract the date from a text string in Excel, you can use the following formula:

=MID($A1,SEARCH("/??/",$A1,1)-2,10)

where A1 is the cell that contains the text string.

This formula uses the SEARCH function to locate the position of the date part in the string and the MID function to extract the date part of the string.

Extract Date from Text String

3. Extract Year from Date

If you wish to extract the year part from a date value in Excel, you can use the YEAR function.

extract year from a date value1

The YEAR function takes a date as an argument and returns the year component of the date, as a number.

 For example, if cell A1 contains the date “02/08/2022“, you can extract the year using the following formula:

=YEAR(A1)

This formula will return the year “2022“.

4. Concatenate Date and Time

If you want to concatenate date and time in Microsoft Excel, you can use the “&” operator in combination with TEXT function to join the values in separate cells into one cell.

The Steps are as below:

Step 1: Enter the following formula in a empty cell:

=TEXT(A2,"MM/DD/YYYY") & " " & TEXT(B2,"HH:MM:SS")
  • Replace A2 and B2 with the cells that contain your date and time, respectively.
  • The formula uses the TEXT function to format the date and time values, and then joins them with a space in between.

Step 2: Press Enter to see the result of the formula.

concatenate date and time 1

Step 3: If you want to copy the result to multiple cells, simply select the cell with the formula, and then drag the fill handle to the cells you want to copy the formula to.

concatenate date and time 1

Your date and time values should now be combined into one cell in the format of “MM/DD/YYYY HH:MM:SS“.

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

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

extract time and hour1

Step 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

Step 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

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

7. Split Date and Time in Excel

If you want to split a date and time value into separate date and time values in Microsoft Excel, and you can use the above TEXT formula to Extract Date portion from date and time value.

Then you can use the below Text formula to extract the time portion of the date and time value:

=TEXT(A2,”HH:MM:SS”)

Alternatively, you can also use the INT function to extract the date portion of the date and time value, enter the following formula in a new cell:

=INT(A2)

Enter the following formula in another cell to extract the time portion:

=A2-INT(A1)
split date and time in excel1

These formulas will return the date and time portions of the date and time value as serial numbers, which can be formatted as dates and times using the “Number Format” option in the “Home” tab of the Excel ribbon.

8. Convert Unix Timestamp to a Date in Excel

If you wish to convert a Unix timestamp to a date in Microsoft Excel, you can use the following TEXT formula:

=TEXT((A1/86400)+DATE(1970,1,1),"MM/DD/YYYY HH:MM:SS")
convert unix timestamp to date1

In this formula, “A1” represents the cell that contains the Unix timestamp. The Unix timestamp is divided by 86400 to convert it to the number of days since January 1, 1970. The result is then added to the date “1/1/1970” to give the date and time corresponding to the Unix timestamp. The “TEXT()” function is used to format the result as a date and time in the “MM/DD/YYYY HH:MM:SS” format.

Note that the Unix timestamp is typically stored as an integer value representing the number of seconds since January 1, 1970, so you may need to divide the value in the cell by 86400 to convert it to a number of days.

9. If function with dates

The IF function in Microsoft Excel can be used with dates to perform conditional tests based on the values of dates. Here’s the general syntax for using the IF function with dates:

=IF(condition, [value_if_true], [value_if_false])

In this syntax, “condition” is the logical test that you want to perform, [value_if_true] is the value that will be returned if the condition is true, and [value_if_false] is the value that will be returned if the condition is false.

Here’s an example of using the IF function with dates:

=IF(A1>B1, "A1 is later", "B1 is later")
if function with dates1

In this example, the condition “A1>B1” checks if the date in cell A1 is later than the date in cell B1. If the condition is true, the result will be “A1 is later“, and if the condition is false, the result will be “B1 is later“.

Note: When working with dates in Excel, it’s important to ensure that the dates are stored as dates and not as text. You can format a cell as a date by selecting it, right-clicking, selecting “Format Cells“, and then selecting the “Date” category under “Number“. From the available date formats, select the one you want to use and click “OK“.

10. Time Calculations in Excel

You can perform various calculations with times to get the result in terms of hours, minutes, or seconds. Here are a few examples of time calculations in Excel:

Add two times:

=A1+B1
add two times in excel

In this example, the two times in cells A1 and B1 are added together to give the result in the same time format.

Subtract one time from another:

=A1-B1
subtract two times in excel

In this example, the time in cell B1 is subtracted from the time in cell A1 to give the result in terms of a time.

Calculate the difference between two times in hours:

=(A1-B1)*24
calculate the difference between two times in hours

In this example, the difference between the two times is calculated in terms of hours by multiplying the result of the subtraction by 24.

Calculate the difference between two times in minutes:

=(A1-B1)*1440
calculate the difference between two times in minutes

In this example, the difference between the two times is calculated in terms of minutes by multiplying the result of the subtraction by 1440 (24 hours * 60 minutes).

11. Conclusion

In conclusion, extracting dates or times from datetime values in Microsoft Excel is very useful for data analysis and manipulation. Whether you need to separate dates and times for the purpose of sorting or calculating, or just to display the information in a different format, there are several ways to achieve it. Excel offers a range of options for extracting date or time information from datetime values.

12. 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)…

How to Extract Time From A Date And Time in Excel

This post will guide you how to extract time portion from a date and time in Excel. How do I remove date on Date/Time Stamp with a formula in Excel. How to remove date from a date and time value with find and replace feature in Excel 2013/2016.

Assuming that you have a list of data in range B1:B4 in which contain date and time stamps. You want to pull out the time portion only from the given cells in Excel. This post will show you two methods to remote the date from a cell with date and time in it.

Method1: Extract Time from a Date and Time Using Formula


You can remove the date portion from a date and time stamps by using the TIME function, HOUR function, MINUTE function, and SECOND function. Just see the example below, removing the date portion from those values in range B1:B4 using the following formula:

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

Type this formula into a blank cell and press Enter key on the keyboard, and drag the AutoFill Handle down to other cells to apply this formula.

extract time from date and time1

Let’s see how this formula works:

The HOUR, MINUTE and SECOND functions are used to extract values for the Hour, Minute and Second portions. Then the TIME function will format those values as a Time stamp.

You can also use another formula based on the INT function to achieve the same result. Like this:

=B1-INT(B1)

Type this formula into a blank cell and press Enter key, then drag the AutoFill handle over to the other cells that you want to apply the same formula. The serial number of time portion would be extracted. Then you need to reformat those cells to a time format, it will allow the fraction to show as a normal Time value.

extract time from date and time2

extract time from date and time3

 

Method2: Extract Time from a Date and Time Using Find and Replace


You can also use Find and Replace feature to extract time portion from a date and time stamp in Excel. Just do the following steps:

Step1: select the range of cells that contain date and time values.

extract time from date and time4

Step2: right click on the selected cells, and choose Format Cells from the dropdown menu list. And the Format Cells dialog will open.

extract time from date and time5

Step3: select General category under the Category drop list, it will change the normal date and time forma to general format. click Ok button to return to the current worksheet.

extract time from date and time6

Step4: go to Home tab in the Excel Ribbon, click Find & Select command under Editing group, and choose Replace from the dropdown list. And the Find and Replace dialog will open.

extract time from date and time7

Step5: enter the pattern *. Into the Find what text box, and enter the another pattern . into the Replace with text box in the Find and Replace dialog box.

extract time from date and time8

Step6: click Replace All button, click close button to back to the current worksheet. You would get the serial number of the time portion for all the selected date and time values.

extract time from date and time9

Step7: keep those values selected, and right click on it, and select Format cells, and choose Time category under the Category drop list. And select one type in the Type list box, click Ok button.

extract time from date and time10

Step8: you would see that all time portions have been extracted.

extract time from date and time11

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)…

Excel SECOND Function

This post will guide you how to use Excel SECOND function with syntax and examples in Microsoft excel.

Description

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.

For example, if you pass a time of 6:32:54 PM into SECOND function, and it will return 54. So you can use this function to extract the second component from a time value.

The SECOND function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The SECOND function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the SECOND function is as below:

=SECOND (serial_number)

Where the SECOND function arguments is:

Serial_text -This is a required argument. The time from which you want to extract the second component. it may be typed as text string within quotation marks (for example, “13:52 PM”), or it can be a decimal numbers(for example, 0.5), or the result of a formula (for example, TIMEVALUE(“13.54:30 PM”))

Note:

  • A serial date is how the Microsoft Excel stores dates and times and it represents the number of days since 1900-01-01, so the January 1, 1900 date is serial number 1 by default.
  • If the serial_number is not a valid Excel time, it will return #VALUE! Error.

Excel SECOND Function Examples

The below examples will show you how to use Excel SECOND Function to return the seconds of a given time value.

1# get the seconds from an Excel time 6:38:45, enter the following formula in Cell B1.

=SECOND(6:38:45)

excel second example1

2# Get the second portion of the date with no second portion specified, enter the following formula in Cell B3.

=SECOND("6:38")

or

=SECOND(TIMEVALUE("6:38 PM"))

excel second example2

It will return 0.


Related Functions

  • 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. And the return value is between 0-23.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 DAY function
    The Excel DAY function returns a day of a date (from 1 to 31). The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel TIMEVALUE Function
    The Excel TIMEVALUE function returns the decimal number of the time represented by a text string. so it will convert a time represented by a text string into an Excel time value.The syntax of the TIMEVALUE function is as below:=TIMEVALUE (time_text)…

More Excel Second Function Examples