04 Alternative Ways: How to Change Valid Excel Date Format

04 Alternative Ways: How to Change Valid Excel Date Format?

By

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? 

 

Formatting of dates in a valid format-1

 


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.  

Formatting of dates in a valid format-2

 


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.

Formatting of dates in a valid format-3

 


C. Valid Excel Date Format: Using the ‘Format Cells’ dialog box through the Ribbon

Formatting of dates in a valid format-4

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.

Formatting of dates in a valid format-5

 

Finally, press OK or press Enter on the keyboard. We get the result in the desired format as shown below:

Formatting of dates in a valid format-6

 


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: 

Format Dates into a Valid Excel Date Format Using Text to Columns-1

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’.

Format Dates into a Valid Excel Date Format Using Text to Columns-2

 

• In the second step, uncheck all the boxes, if any press ‘Enter’ or ‘Next’.

Format Dates into a Valid Excel Date Format Using Text to Columns-3

 

• In the third step, select the ‘Date’ format press ‘Enter’ or ‘Finish’.

Format Dates into a Valid Excel Date Format Using Text to Columns-4

 

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.       

Format Dates into a Valid Excel Date Format Using Text to Columns-5

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.  

WHY DATE FORMAT NOT CHANGING IN EXCEL-1

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’

WHY DATE FORMAT NOT CHANGING IN EXCEL-3

 

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.

WHY DATE FORMAT NOT CHANGING IN EXCEL-2

 

Finally, the Excel date format is converted into values. 

WHY DATE FORMAT NOT CHANGING IN EXCEL-4

 

• 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 ‘Delimitedpress Enter or Next.

WHY DATE FORMAT NOT CHANGING IN EXCEL-5

 

In the second step, uncheck all the boxes, if any then press Enter or Next.

WHY DATE FORMAT NOT CHANGING IN EXCEL-6

 

In the third step, select the ‘Date‘ format press Enter or Finish.

WHY DATE FORMAT NOT CHANGING IN EXCEL-7

 

Finally, the range seems to be the same as before, but it is now ready to convert into the proper date format.  

WHY DATE FORMAT NOT CHANGING IN EXCEL-8

 

• Step-4: Lastly, select the range press Excel Shortcut Ctrl+Shift+# to apply and arrange the valid Excel date format. 

 

WHY DATE FORMAT NOT CHANGING IN EXCEL-9

 


Learn-More-5

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

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

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

Sudipta Mondal
Follow AEF
Advance Excel Forum

04 Alternative Ways: How to Change Valid Excel Date Format

Editor's Rating:
5

Leave a Comment

You may also like