How to Extract Date and Time in Excel

 How to Extract Date and Time in Excel

Figure 01

How can we extract the date and time that contain in a single cell in Excel. The sample data on the figure 02 shows that all data including Date and Time contained in the same cell. The data display as Date & Time format.

This kind of data we can use the command on the Ribbon "Text to Column" and the Date & Time functions to extract the date and time and store separately.

Figure 02

Firstly, create the header: Date, Time and Meridiem Indicator to column B, C and D respectively.

Figure 03

Next let's select all cells that contain the date and time data, then go to the Ribbon and find the tab Data, then click on the "Text to Columns".

Figure 04

The window Convert Text to Columns Wizard will prompt immediately. Let's choose on the "Delimited", this is because our data separate by "space". Then click "Next" and go to step 2.

Figure 05

Since the data separate by space, so we tick on the "Space", the we will see the line separate the Date, Time and Meridiem Indicator into three columns. Click "Next" to go to the step 3.

Figure 06

In the step 3, the column data format we choose "General". The "Destination" we choose cell B2 for first cell that contain the first date we extract from the cell A2. Then click "Finish".

Figure 07

Now you got the Date, Time and Time Indicator as the Figure 9.

Figure 09

Now we will use functions to extract the date and time.

Please note that, before we will use the function DATE and TIME we have to make sure that the data is Numeric, otherwise it doesn't work. We start with create the header "Date" and "Time" to the column B and C respectively.

Figure 10

We starting extract the Date first. On the cell B2, use this function: 

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

Then copy down this function to cell B10 you'll got all dates.

Figure 11

Explaination: 

The syntax of DATE function is: DATE(year, month, day). To extract the "year" we use the function YEAR, to extract "month" we use function MONTH and to extract th "day" we use the function DAY.

Now we continue extracting the "time" using the TIME function. On the cell C2 use this function:

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

The just copy down to the cell C10 we'll got all times.

Figure 12

Explaination: 

The syntax of TIME function is: TIME(hour, minute, second). 

To extract the "hour" from cell A2 we use the HOUR function. To extract the "minute" from cell A2 we use MINUTE function. And to extract "second" from cell A2 we use function "SECOND".

Figure 13

Figure 14