Converting the Excel date time format to Date only format.

Converting the Excel date time format to Date only format.

There are number of times when we encounter Date in the form “date and time”.

E.g.

Typically when a user extracts data from a tool to calculate metrics, the date fields “Created Date” and “Updated Date” are in the format DATE and TIME. For some reason, we would like to have these date fields converted to DATE ONLY fields.

How can we do convert this DATE TIME field value to DATE only value?

Refer to the figure below. You want to change the DATE and TIME to DATE ONLY format.

Excel Date and Time
Excel Date and Time

  Step 1: Insert two blank columns to the right of the DATE field column.

  

Insert 2 blank columns to the right
Insert 2 blank columns to the right

  Step 2 : Select the cells containing the DATE value and Choose the command DATA >> TEXT TO COLUMNS. A wizard screen will be invoked.

 

  

Select DATA >> TEXT to COLUMNS
Select DATA >> TEXT to COLUMNS

 Step 3 : Select DELIMITED option in the Step 1 of the wizard and click NEXT.

Select DATE >> Text to Columns
Select the option "Delimited"

 Step 4 : Change the Delimited to SPACE. Note the cell preview shown below which indicates distinct Date and Time segregation. Click NEXT.

 

Change Delimited to SPACE
Change Delimited to SPACE

 

 Step 5 : Select the column date format as “DATE” and also indicate the format in which you would like the date to appear. Click FINISH.

  

Select the appropriate DATE format
Select the appropriate DATE format

 

Step 6 : Your new date values have appeared in the cells. However there is still the time component. You will need to change the format of the date from DATE and TIME to DATE ONLY.

Select the right date format
Select the right date format

 

 

  

 

 

For this, right click the selected Date cells and select “FORMAT CELLS” from the short cut menu. Ensure that you select the DATE and the format showing the DATE part only.

   

On your way to DATE ONLY
On your way to DATE ONLY

 

DATE and TIME to DATE ONLY format
DATE and TIME to DATE ONLY format

 

VIOLA!!! You have converted your DATE and TIME value cells to DATE ONLY values.

Advertisements

8 thoughts on “Converting the Excel date time format to Date only format.”

  1. This was great! It actually converts to a bonafide date format, not just text that looks like a date. Now the new date value can be sorted and otherwise used as an actual date! Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s