# How to Change Date Alignment in Excel: 8 Simple Methods

### Method 1 – Change Date Alignment with Text to Columns Feature

STEPS:

• Select the date range D5:D8.
• Go to Data > Data Tools > Text to Columns.

• A dialog box will pop out.
• Select Delimited and press Next.

• Press Next in the step 2 dialog box.
• In step 3, choose Date and DMY from the drop-down options.
• Press Finish.

• Youâ€™ll see the accurate date alignment.

### Method 2 – Insert VALUE Function to Align Date in Excel

STEPS:

• Select cell E5.
• Type the formula:
`=VALUE(D5)`
• Press Enter and use AutoFill to get all the results.

• Select the range E5:E8.
• Choose the Date format from the Number drop-down in the HomeÂ tab.
• Return the desired output.

### Method 3 – Align Date Using DATEVALUE Function

STEPS:

• Click cell E5.
• Input the formula:
`=DATEVALUE(D5)`
• Press Enter.
• Apply AutoFill.

• Select the range and choose Number > Date under the HomeÂ tab.
• Get the precise date alignment.

### Method 4 – Set Date Alignment with Excel Find & Replace Feature

STEPS:

• Select the range D5:D8 at first.

• Press the Ctrl and H keys together.
• The Find and Replace box will emerge.
• Insert in Find what and / in Replace with.
• Click Replace All.

• Return the worksheet after making the required corrections.
• You can change the date alignment.

### Method 5 – Combine VALUE and SUBSTITUTE Functions for Fixing Date Alignment

STEPS:

• Choose cell E5.
• Insert the formula:
`=VALUE(SUBSTITUTE(D5,".","/"))`
• Press Enter and use AutoFill.
• The SUBSTITUTE function replaces period (.) with / in cell D5.
• The VALUE function converts it to a number.

• Select the range E5:E8.
• Choose Number > Date as the number format.
• Perfectly aligned dates will appear.

### Method 6 – Align Date with Combination of RIGHT, LEFT, MID, DATE & FIND Functions

STEPS:

• Click cell E5.
• Type the formula:
`=FIND("/",D5)`
• Press Enter and apply AutoFill.

• In cell F5, input:
`=FIND("/",D5,1+FIND("/",D5))`
• Hit Enter.
• This formula looks for the second / symbol and returns its position.
• Use AutoFill.

• Choose cell G5 to insert the formula:
`=LEFT(D5,E5-1)`
• Click Enter.
• Return other results by using AutoFill.

• In the H5 cell, insert:
`=MID(D5,E5+1,F5-E5-1)`
• Return output by pressing Enter.
• Use AutoFill.

• Choose I5 for the formula:
`=RIGHT(D5,4)`
• Click Enter and apply AutoFill.

• In cell J5, insert:
`=DATE(I5,H5,G5)`
• This formula will return accurate date alignment after pressing Enter.
• Apply AutoFill to get other results.

### Method 7 – Apply Excel Error Checking Feature to Change Date Alignment

STEPS:

• Go to File > Options.
• Make sure you check the boxes for the marked options in the FormulasÂ tab.
• See the below picture for a better understanding.

• You have the year in only 2 digits, Excel will show a green shade on the upper-left side of the cell.
• Select the cell, and youâ€™ll see the warning symbol.
• Click the drop-down symbol and choose Convert XX to 20XX.

• Itâ€™ll return the date in perfect alignment.

### Method 8 – Align Date with Excel Power Query

STEPS:

• Select the range B4:D8.
• Go to Data > Get & Transform Data > From Table/Range.

• A dialog box will appear and press OK.
• Select the Date column.
• Click Split Column > By Delimiter.

• In the pop-out dialog box, type / in the Custom delimiter.
• Press OK.

• The column will be split into 3 columns.
• Select the month, day, and year columns at the same time.
• Click Transform > Text Column > Merge Columns.

• In the dialog box, type / in the custom separator.
• Input the new column name.
• Press OK.

• Click the entire merged column and choose Data Type > Date.

• Youâ€™ll get a new Excel worksheet with the required date alignment.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelorâ€™s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF