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

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

  

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

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

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

 

 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

 

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

 

 

  

 

 

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

 

DATE and TIME to DATE ONLY format

 

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