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”.
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.
Step 1: Insert two blank columns to the right of the DATE field column.
Step 2 : Select the cells containing the DATE value and Choose the command DATA >> TEXT TO COLUMNS. A wizard screen will be invoked.
Step 3 : Select DELIMITED option in the Step 1 of the wizard and click NEXT.
Step 4 : Change the Delimited to SPACE. Note the cell preview shown below which indicates distinct Date and Time segregation. Click NEXT.
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.
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.
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.
VIOLA!!! You have converted your DATE and TIME value cells to DATE ONLY values.