04 Alternatives: How to Extract Date from Timestamp Excel

04 Best Ways: How to Extract Date from Timestamp Excel?

By

Extract Date from Timestamp Excel – a big problem to identify those dates and how to extract them?

Cells having ‘Date with time‘ and ‘Date without time‘ – it does matter in a sense, but it really matters in data analysis, MIS preparation when we compare with the two dates.  

Suppose, we have a large master data with some activity dates and want to recheck these dates from another file for billing purposes, both dates are not matched to each other even they are same dates and it was then painful for anyone.

There are 4 methods to extract date from timestamp Excel:

(01) By using the Text to Columns;

(02) By using the INT () function

(03) By using the TRUNC () function

(04) By using the CONCATENATE () function 


I. How to Identify the cells having date & time altogether?

Firstly, we need to identify those cells before extract date from timestamp Excel.

We can identify those cells having both date and time altogether in two ways:

(01) By comparing with two dates

(02) By changing the date format into the general format

 

A. Method 1: By comparing with two dates

While comparing two dates (of course, one date should have no timestamp), we can easily identify the cells having the date and time altogether.  We can compare through the ‘True’ and ‘False’ method. The Syntax is: =A2=B2 and copied down the formula till the end of the range. 

  • True – indicates both dates are unique.
  • False – indicates one of them having a time extension.
Identify the cells having date & time altogether

B. Method 2: By changing the date format into the general format

Alternatively, if we can convert the date format into the general format, we find that some cell values having point extensions, that indicate that those cells having a date with a time.

We can do this in three ways, but before we proceed to select the column range individually (A2:A14) or entirely (A2:B14).

(01) To Apply the ‘General’ Format: using of Excel Shortcut

(02) To Apply the ‘General’ Format: using of Alternative Excel Shortcut

(03) To Apply the ‘General’ Format: using the Ribbon

 

(01) To apply the ‘General’ Format: using of Excel Shortcut (Alt+H+N)

It is the smart way using the Excel shortcut to apply the ‘General’ format.

Press Alt+H+N on the keyboard to activate ‘Number’ Format box under ‘Home‘ tab ➪ then just type ‘Gen….‘ automatically appears ‘General’ category in the box or we can select the ‘General’ category from the drop-down list ➪ then press Enter.

Identify the cells having date & time altogether - apply the general format using Excel shortcut

(02) To apply the ‘General’ Format: using of Alternative Excel Shortcut

Alternatively, we can use another Excel shortcut to apply the ‘General’ format.

Press Ctrl+1 on the keyboard ➪ click on ‘Number‘ tab ➪ select the ‘General format ➪ click  OK or press Enter.

Identify the cells having date & time altogether - apply the general format using another Excel shortcut

(03) To apply the ‘General’ Format: using the Ribbon

It is the manual process. 

Go to the Home tab ➪  click on ‘Alignment settings’ on the right side bottom corner under the Alignment section, or click on ‘Number settings’ on the right side bottom corner under the Number section ➪ opens ‘Format Cells’ wizard ➪ go to ‘Number’ tab ➪ select ‘General’ ➪ click on OK or press Enter.

Identify the cells having date & time altogether - apply the general format manually

After applying one of the above mentioned three methods, the Date format will be changed in the General format.

The same method should apply in both the date columns and it seems to be:

Identify the cells having date & time altogether - showing point extension after the values due to converting date format to general format

II. How to Extract Date from Timestamp Excel?

A. Extract Date from Timestamp: using the ‘Text to Columns Wizard’

‘Convert Text to Columns Wizard’ is one of the best ways to extract date from timestamp. The process is as follows:

  1. Changing the date format into the general format

  2. Split the point extension after the values 

  3. Change the general format into the date format

 

(01). Changing the date format into the general format

Already mentioned the process above, just follow it. 

 

(02). Split the point extension after the values

In the next step, after identifying the cells or columns, we have to use the ‘Convert Text to Columns Wizard‘ to remove/split the point extensions from the values.

• Add two more columns in-between ‘Date with Time’ and ‘Date without Time’ by using excel shortcut Ctrl + plus (+).

Read more about the use of advanced excel shortcuts.

• Select the range A2:A14.

• Press Alt+D+E or Alt+A+E on the keyboard that will open ‘Convert Text to Columns Wizard’.

• In Step 1, we select Delimited (default option) and then click on Next or Enter.

Split the point extension after the values - first step

 

• In Step 2, select the only checkbox ‘Other’ and enter a point (.) in the box to the right of it because the point is used here as a delimiter. Make sure that other checkboxes should be unchecked (if any). Click on Next or Enter

Split the point extension after the values - second step

 

• In Step 3, select the destination cell in B2 to avoid the overwriting of existing data.

Split the point extension after the values - third step

• Click on Finish or press Enter on the keyboard.

A new window appears and asking for replacing the existing data. Click on OK or press Enter

Split the point extension after the values - fourth step

• This would instantly give the results in two parts – the integer part in one column and the fraction part in another column.

Split the point extension after the values - fifth step

(03). Change the general format into the date format

Select both the columns B and D by pressing ‘Ctrl’ key and then press Ctrl+Shift+# for date formatting.

Change the general format into the date format

 

(04). Rearrange the data properly

Rearrange the data properly is a very common word but it is very important in data analysis and report preparation. After copied the date ranges B2:B14, select the cell A2 (the starting cell where to paste the dates) by a single click on the mouse.  

Remember that we should try to avoid the normal paste (Ctrl+V), rather we would like to use ‘Values and number formats(Alt+Ctrl+V+U or Alt+E+S+U) or ‘Values‘ (Alt+Ctrl+V+V or Alt+E+S+V) under the Paste Special dialog box.

The reason behind this is to keep our cell formatting (like font, font size, font color, text color, etc.) as it is as already have previously.  

The entire range would instantly convert from formulas to values.

Rearrange the data properly

After pasting the dates from column B to column A, please notice that all the ‘False’ cells become ‘True’.

Then select the unused columns like B and C and delete those by using the shortcut Ctrl + minus (-).

That’s it!

Rearrange the data properly - deleting unused columns

B. Extract Date from Timestamp: using the INT() Function

Extract Date from Timestamp If we have dates with time values and we want to extract only the date portion, we can use a formula that uses the INT function.

Excel identifies the date-time format as numbers with two parts – (i) serial numbers as dates and (ii) fractional values as times. For example, Excel recognizes  06-10-2019  14:11 PM as the number 43744.59, where 43744 is the date part and .59 is the time part.

So in that case, we can extract date from date-time format by using a formula that uses either INT function or TRUNC function.  Suppose, cell A1 contains the date-time format, 06-10-2019  14:11 PM, the formula has returned the date part 06-10-2019 as number format (43744).

The Syntax of INT() function is: =INT(number)

Using the INT() Function: Extract Date from Timestamp


C. Extract Date from Timestamp: using the TRUNC() Function 

Alternatively, TRUNC() is the function that simply extracts date from the timestamp, removing the extra digits without performing any rounding.

However, INT() function is the simpler of the two, as it always rounds to whole numbers. We need only specify the number we want rounded. 

In each case, Excel discards the decimal portion. TRUNC () is similar to INT (), except it uses a second argument specifying the number of decimal places we want to preserve. This argument is optional, and if we leave it out, TRUNC () and INT () behave exactly the same with positive numbers.

The Syntax of TRUNC function is: =TRUNC(number_to_round, [number_of_digits])

Using the TRUNC() Function: Extract Date from Timestamp


D. Extract Date from Timestamp: using the CONCATENATE() Function

We can use CONCATENATE() function to extract date from timestamp. 

The Syntax of the CONCATENATE function is: =CONCATENATE(text1, [text2],…)

(01). The process of using the CONCATENATE function

• Select the cell (i.e., B2) where to apply the CONCATENATE function. Type ‘con…‘ and select the CONCATENATE function from the drop-down suggestion list with the help of a down arrow (↓). Remember that upper or lower case doesn’t matter. 

• Then press the ‘Tab‘ key which allows the opening of CONCATENATE syntax with an open parenthesis. 

• We allow DAY () function within the CONCATENATE function to capture the date from the date-time format. Within the DAY function, select the cell and close the parenthesis. Then place a comma that allows moving to the next argument of the syntax. 

• As a next argument, we use a delimiter slash ‘/‘ or dash ‘‘ inside the double quotation marks as EXCEL read them as text.

Using the CONCATENATE() Function-Extract Date from Timestamp_1

 

• Then we allow the next function MONTH () to capture the month from the date-time format. As similar to the DAY function, select the cell and close the parenthesis. Then place a comma that allows moving to the next argument of the syntax. 

• The next argument, again we use a delimiter slash ‘/‘ or dash ‘‘ inside the double quotation marks as EXCEL read them as text.

• Then we use the YEAR () function to capture the year. As similar to the above function, select the cell and close the parenthesis.

If we didn’t close the last parenthesis and press Enter, a suggestion pop-up of Microsoft Excel appears. Again press Enter or click on ‘Yes‘, indicating that we want to accept the correction and Excel by default closes all the parentheses. 

• A combination of all functions to capture the date from the timestamp. Copy the formula till the end of the range.     

Using the CONCATENATE() Function-Extract Date from Timestamp_02

 

• Make the formula range into values with the help of Paste Special dialog box. Copy the range press Alt+E+S+U or Alt+Ctrl+V+U to select the ‘Values and number formats‘ option, or press Alt+E+S+V or Alt+Ctrl+V+V to select the ‘Values‘ option press Enter or Click on OK to accept the condition. 

Using the CONCATENATE() Function-Extract Date from Timestamp_3

 

• The date range is not converted into a valid date format by using the Excel shortcut Ctrl+Shift+# or applying the date format under the ‘Format Cells‘ dialog box. There is an easy solution using the ‘Convert Text to Columns Wizard‘.

• Select the range press Alt+A+E or Alt+D+E to open the ‘Convert Text to Columns Wizard select ‘Delimited‘ ➪ press Enter or click on ‘Next‘.   

Using the CONCATENATE() Function-Extract Date from Timestamp_4

 

•  In the next step, uncheck all the checkboxes press Enter or click on ‘Next’.

Using the CONCATENATE() Function-Extract Date from Timestamp_5

 

• In the final step, select the ‘Date‘ option under the ‘Column date format’ section. No need to change the Destination cell press Enter or click on ‘Finish’ to accept the condition.  

• As a result, all dates will be converted into a valid date format. 

Using the CONCATENATE() Function-Extract Date from Timestamp_6

 

(02). Extract Date from Timestamp:  Using the Alternate CONCATENATE function

We can use the ampersand sign (&) to concatenate. 

Concatenate Date(), Month() and Year() function with the ampersand sign. We should use the slash ‘/’ or dash’-‘ as a delimiter in between the separate functions and make concatenate using the ampersand sign (&). 

Now, the formula is =DAY(A2) &‘/’&MONTH(A2) &‘/’&YEAR (A2)

Alternatively,  =DAY(A2) &‘-‘&MONTH(A2) &‘-‘&YEAR (A2)

Extract Date from Timestamp:  Using the Alternate CONCATENATE function:


Conclusion

How to Extract Date from Timestamp Excel

 

If in our working database having a combined date and time, then we should use one of the following steps to extract date from timestamp:  

(01) We should identify those cells- 

• Convert date format to general format

(02) After identification, use one of the following methods to extract date from timestamp

• Using ‘Text to Columns wizard

(i) Convert date format to general format

(ii) Split the point extension after the values

(iii) Convert general format to date format 

(iv) Finally, rearrange the data properly

• Using ‘INT ()‘ function

• Using ‘TRUNC ()‘ function

• Using ‘Concatenate ()‘ function.



Read-More_2

• 10 Examples of Text to Columns || How to Split Cells/Columns in Excel

• 90+ Best Excel Shortcut Keys and Hotkeys with CTRL Key

• 80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts


Follow AEF
Sudipta Mondal is a writer, author as well as founder of "Advance Excel Forum (AEF)". It is the best learning platform on Advance Excel.

According to Sudipta, "Advanced Excel is a very necessary modern tool in the advanced world that can only be achieved through a committed focus and self-analyzing skills".
Sudipta Mondal
Follow AEF
Advance Excel Forum

04 Alternatives: How to Extract Date from Timestamp Excel?

Editor's Rating:
5

Leave a Comment

You may also like