How to Change Date Alignment in Excel (8 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use Excel files to store valuable data. Sometimes, we see the date in the wrong alignment in Excel worksheets. In that case, excel can’t recognize the cells as the date input. This will result in errors. This article will show you 8 simple methods to change Date Alignment in Excel.


How to Change Date Alignment in Excel: 8 Simple Methods

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains the Invoice Date. Notice that, the values are left-aligned in Excel. Excel can’t detect them as the date. Excel automatically sets a date to the right alignment if it recognizes it. Again, the default date format is mm/dd/yyyy in Excel. In the dataset, it’s in the dd/mm/yyyy format. So, go through the methods to change Date Alignment in Excel.

date alignment in excel


1. Change Date Alignment with Text to Columns Feature

The Text to Columns feature helps a column to rearrange by using the delimiter. In our first method, we’ll apply this feature to change date alignment. Therefore, follow the steps below to perform the task.

STEPS:

  • First, select the date range D5:D8.
  • Then, go to Data > Data Tools > Text to Columns.

Change Date Alignment with Text to Columns Feature

  • As a result, a dialog box will pop out.
  • Select Delimited and press Next.

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

  • Finally, you’ll see the accurate date alignment.

Read More: How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel


2. Insert VALUE Function to Align Date in Excel

The VALUE function converts a text value that seems to be a number to a number value. We’ll first convert the inaccurate date range to the number. Then, we’ll set the date format to get the outcome. The date inputs are separated by – instead of/in this example. In this way, excel thinks they’re numbers. So, learn the following steps.

STEPS:

  • Firstly, select cell E5.
  • After that, type the formula:
=VALUE(D5)
  • Press Enter and use AutoFill to get all the results.

Insert VALUE Function to Align Date in Excel

  • Subsequently, select the range E5:E8.
  • Choose the Date format from the Number drop-down in the Home tab.
  • Thus, it’ll return the desired output.


3. Align Date Using DATEVALUE Function

Moreover, the DATEVALUE function transforms a text value that appears to be a date into a number value. Afterward, we’ll format the function output to get the desired date alignment. Hence, learn the process.

STEPS:

  • First of all, click cell E5.
  • Then, input the formula:
=DATEVALUE(D5)
  • Next, press Enter.
  • Apply AutoFill.

Align Date Using DATEVALUE Function

  • Afterward, select the range and choose Number > Date under the Home tab.
  • Consequently, you’ll get the precise date alignment.


4. Set Date Alignment with Excel Find & Replace Feature

However, if you have a period (.) between the month, day, and year, you can use the Find & Replace feature. Therefore, follow the process to carry out the operation.

STEPS:

  • Select the range D5:D8 at first.

Set Date Alignment with Excel Find & Replace Feature

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

  • Hence, it’ll return the worksheet after making the required corrections.
  • In this way, you can change the date alignment.


5. Combine VALUE and SUBSTITUTE Functions for Fixing Date Alignment

An alternative process to the Find & Replace feature is the combination of the VALUE and the SUBSTITUTE functions. The SUBSTITUTE function replaces the specified sign or text with a new one in the specified text string. So, follow the steps.

STEPS:

  • In the beginning, choose cell E5.
  • Insert the formula:
=VALUE(SUBSTITUTE(D5,".","/"))
  • Next, press Enter and use AutoFill.
  • Here, the SUBSTITUTE function replaces period (.) with / in cell D5.
  • Then, the VALUE function converts it to a number.

Combine VALUE and SUBSTITUTE Functions for Fixing Date Alignment

  • Select the range E5:E8.
  • Choose Number > Date as the number format.
  • As a result, perfectly aligned dates will appear.


6. Align Date with Combination of RIGHT, LEFT, MID, DATE & FIND Functions

We can also take multiple steps for changing the date alignment using different functions in each step. The RIGHT function returns texts from the right-most side of the text string, whereas the LEFT function returns from the left-most side. We can get texts from the middle using the MID function. The DATE function gives out the date output. And lastly, the FIND function gives out the position of a specific text or symbol in the text string. Therefore, to get the job done, see the steps carefully.

STEPS:

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

Align Date with Combination of RIGHT, LEFT, MID, DATE & FIND Functions

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

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

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

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

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

date alignment output from combination of functions


7. Apply Excel Error Checking Feature to Change Date Alignment

Excel has an amazing feature to detect errors and will suggest some solutions. We’ll make use of this feature in this method. So, follow the process.

STEPS:

  • To get the feature, 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.

Apply Excel Error Checking Feature to Change Date Alignment

  • Now, if 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.

  • As a result, it’ll return the date in perfect alignment.


8. Align Date with Excel Power Query

The Power Query can perform various operations by taking the dataset from the Excel worksheet without affecting the original dataset. In our last method, we’ll use this feature. Therefore, follow along.

STEPS:

  • Firstly, select the range B4:D8.
  • Then, go to Data > Get & Transform Data > From Table/Range.

Align Date with Excel Power Query

  • A dialog box will appear and press OK.
  • Now, select the Date column.
  • Afterward, click Split Column > By Delimiter.

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

  • As a result, the column will get split into 3 columns.
  • Next, select the month, day, and year columns respectively at the same time.
  • Accordingly, click Transform > Text Column > Merge Columns.

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

  • Subsequently, click the entire merged column and choose Data Type > Date.
  • Lastly, press Close & Load.

  • Thus, you’ll get a new excel worksheet with the required date alignment.

date alignment in new worksheet


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to change Date Alignment in Excel using the above-described methods. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo