For MIS preparation and data analysis, we should need to arrange the invalid Excel date format to valid Excel date format such as ‘DD-MMM-YY’ (or any other valid date format).
Sometimes, the date format written as a text, or sometimes while we extract time from a date, Text to Columns is the best way to convert into a valid Excel date format if the rest three methods failed to do so.
But now the question arises in mind how to change date format in Excel?
I. HOW TO CHANGE VALID EXCEL DATE FORMAT?
We can do this in 4 ways:
A. Valid Excel Date Format: By Excel Shortcut Ctrl+Shift+#
Select the date range B2:B14 and then press Ctrl+Shit+#. All are arranged in a valid date format.
B. Valid Excel Date Format: Using the ‘Format Cells’ dialog box by Excel Shortcut Ctrl+1
Alternatively, select the data range B2:B14 ➪ then press ‘Ctrl+1′ to open the Format Cells dialog box ➪ go the ‘Custom‘ option under the Number tab ➪ On the right side, write require date format like dd-mmm-yy under ‘Type‘ section.
C. Valid Excel Date Format: Using the ‘Format Cells’ dialog box through the Ribbon
Alternatively, select the data range B2:B14 ➪ go to Home tab ➪ click on the right side corner of ‘Alignment‘ Settings or ‘Number‘ settings ➪ open the Format Cells dialog box ➪ select the ‘Custom‘ option under the Number tab ➪ On the right side, write the valid date format like dd-mmm-yy under ‘Type‘ section.
Finally, press OK or press Enter on the keyboard. We get the result in the desired format as shown below:
D. Valid Excel Date Format: Using the ‘Convert Text to Columns Wizard’
If one of the above three processes unable to convert to a valid excel date format, then last but the best solution is to apply to Convert Text to Columns.
We can easily convert text to date in excel, invalid date format to valid date format with the help of Text to Columns, but it does not convert number to date in excel. In that case, we apply sequentially Excel Shortcut Ctrl+Shift+# to convert the number to date format.
Examples are given below:
Steps to Start:
• In the first step, select the range ➪ press Alt+D+E or Alt+A+E to open the Convert Text to Columns Wizard ➪ select ‘Delimited’ ➪ press ‘Enter’ or ‘Next’.
• In the second step, uncheck all the boxes, if any ➪ press ‘Enter’ or ‘Next’.
• In the third step, select the ‘Date’ format ➪ press ‘Enter’ or ‘Finish’.
After applying Text to columns, select the range ➪ press Ctrl+Shift+# to convert numbers to valid Excel date format and all are arranged in date format accordingly.
II. WHY IS DATE FORMAT NOT CHANGING IN EXCEL?
It has been observed that after applying the TEXT function, DAY Function, INT function, TRUNC function, CONCATENATE function, etc., date format not changing in Excel by the above-mentioned steps.
In this case, we should go through the several processes mentioned below:
Steps are the following:
• Apply the function (TEXT, CONCATENATE, INT, TRUNC, etc.)
• Convert the formulas into Values or Values and number formats through Paste Special Window.
• Apply date format through Convert Text to Columns Wizard.
• Convert number format to date format through Ctrl+Shift+#.
• Step-1: Suppose there is a list of date and time formats in Excel and we would like to extract date from a timestamp.
In this case, we should apply any one function likes INT function, TRUNC function, CONCATENATE function to extract the date.
But we cannot convert them into a valid date format by using any one of the above mentioned 4 methods likes Ctrl+Shift+# or CONCATENATE function or using the Format Cells wizard.
• Step-2: Then we copy the range by Ctrl+C ➪ press Alt+E+S+V or Alt+Ctrl+V+V to change them into ‘Values’.
Alternatively, copy the range by Ctrl+C ➪ press Alt+E+S+U or Alt+Ctrl+V+U to change them into ‘Values and number formats’ ➪ press Enter or OK.
Finally, the Excel date format is converted into values.
• Step-3: After converting into values, we should apply the ‘Date‘ format through Convert Text to Columns Wizard.
Select the range ➪ press Alt+D+E or Alt+A+E to open the Convert Text to Columns Wizard ➪ select ‘Delimited‘ ➪ press Enter or Next.
In the second step, uncheck all the boxes, if any ➪ then press Enter or Next.
In the third step, select the ‘Date‘ format ➪ press Enter or Finish.
Finally, the range seems to be the same as before, but it is now ready to convert into the proper date format.
• Step-4: Lastly, select the range ➪ press Excel Shortcut Ctrl+Shift+# to apply and arrange the valid Excel date format.
Latest posts by Sudipta Mondal (see all)
- Excel SUMIFS Function - October 27, 2019
- How to use INDEX MATCH Function in Excel with 04 Best Examples - October 13, 2019
- 09 Steps to Reduce Excel File size || Evaluate Existing Formulas || - September 5, 2019
Advance Excel Forum
04 Alternative Ways: How to Change Valid Excel Date Format