How to Align Dates in Excel: Format and Match Them

Formulas & functions · Updated November 20, 2026

To align dates visually, select the date cells and choose an alignment option in Home > Alignment, such as Align Left, Center, or Align Right. If you mean lining up dates in separate lists or tables, first make sure Excel recognizes them as real dates, then sort or match the records by date. The steps below cover both meanings in Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel workbook containing the dates you want to adjust.
  • For matching or sorting, keep each date beside the information that belongs to it, such as a sales amount or event name.
  • If dates came from a text file or another system, know their original date order, such as month/day/year or day/month/year.

Step-by-Step

  1. Decide what “align” means for your sheet. If you only want the dates to sit in a consistent position inside their cells, use the visual alignment controls in Step 2. If you need records from different lists to share the same date order, continue through the data-cleaning and matching steps. You can do both: first make sure the values are real dates, then choose how they appear and where they sit.
  2. Select the date cells and set their visual alignment. Drag across the cells, or click a column heading to select the whole column. On the Home tab, find the Alignment group and choose Align Left, Center, or Align Right. These buttons change where the cell contents sit horizontally; they do not change the date value or its order. Use the vertical alignment buttons in the same group if you want dates positioned at the top, middle, or bottom of tall cells. If you want a consistent display, select the cells before choosing the alignment so that all of them receive the same setting.
  3. Apply a consistent date format. With the date cells still selected, go to Home > Number Format and choose Short Date or Long Date. For a specific style, open Format Cells with Ctrl+1 on Windows, select the Number tab, choose Date, and select a format. On a Mac, Command+1 opens Format Cells. Choose a format that makes the order clear for your audience; for example, 5 Jan 2026 is less ambiguous internationally than 1/5/2026. Formatting changes how a valid date is displayed, not the underlying date or its alignment.
  4. Check whether Excel recognizes the dates as dates. Click a cell and look at the formula bar. A real date is stored as a number, even when displayed as a date. As a quick check, change one test cell to General using Home > Number Format > General. If it becomes a number such as 46027, Excel recognizes it as a date serial. Undo the test or reapply a date format afterward. If it stays as text, or does not sort chronologically, convert it before matching records.
  5. Convert text dates when necessary. Make a copy of the column first, especially if it contains mixed formats. Select the text-date cells and choose Data > Text to Columns. Choose Delimited, select Next, leave the delimiters unchecked, then select Next. Under Column data format, choose Date and select the order that matches the source data, such as MDY or DMY. Select Finish, then apply a date format. This conversion is useful when all selected entries follow the same known order. Do not choose an order by guesswork: 03/04/2026 could mean March 4 or April 3. For a small number of consistent text dates, a helper column can also convert them. With a text date in A2, use =DATEVALUE(A2) in another cell, fill down, and format the results as dates. DATEVALUE depends on the date text being understandable under your computer’s regional settings.
  6. Sort a date list without separating it from its records. Click a cell in the date column, then choose Data > Sort Oldest to Newest or Data > Sort Newest to Oldest. If Excel asks whether to expand the selection, choose Expand the selection so each row’s other details move with its date. For more control, choose Data > Sort, select the date column under Sort by, and choose Oldest to Newest. Avoid sorting only the date column when neighboring columns contain related values; that would attach the wrong details to each date.
  7. Align two lists that contain the same dates in different orders. First sort each complete table by its date column, using the expand-selection option. If both lists contain the same dates once each, matching rows will then line up. Check for duplicate dates and missing days before relying on the row positions: a missing date in one list shifts every row beneath it, so two records on the same row are not necessarily for the same day.
  8. Match values by date when lists have gaps or different orders. A shared date column is safer than assuming row numbers correspond. For example, if dates are in A2:A5 and their values are in B2:B5, while the dates you want to match are in D2:D6, enter this in E2 and fill down: =XLOOKUP(D2,$A$$2:$A$5,$B$2:$B$5,""). This looks for the date in D2 in the first date list and returns its associated value; it returns a blank when there is no exact match. If A2:A5 contains Jan 3, Jan 5, Jan 8, and Jan 10, 2026, and D2 is Jan 5, the formula returns the value from B3. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Dates in both lists must be genuine Excel dates with matching underlying values; identical-looking text and date values may not match.
  9. Review the finished result. Check the first and last dates, a few dates in the middle, and any blank lookup results. Confirm that row labels and values still belong to the correct dates. If the dates should be displayed uniformly, select the complete date range and apply the same number format and alignment. Save the workbook after checking the result.

Common Mistakes

Using spaces to make dates look centered or lined up. Spaces may appear to work in one column width, but the result changes with font, zoom, or column resizing. Use the alignment buttons instead. Similarly, do not add leading apostrophes or spaces to “fix” date positions; those can turn otherwise valid dates into text.

Confusing formatting with alignment. A date format controls how a value is written, while horizontal alignment controls its position within the cell. Applying Short Date will not center the date, and Center will not convert text into a real date. Set the number format and alignment separately.

Sorting just one column. If dates are sorted without their neighboring records, the amounts, names, or notes can become associated with the wrong days. Sort the whole table or accept Excel’s Expand the selection prompt. If you intentionally need to sort only a date-only list, make sure no related columns need to stay in sequence.

Assuming matching appearance means matching values. Two cells can both display 5/1/2026 while one is a true date and the other is text, or they may represent different days under different regional conventions. Validate the source and convert consistently before sorting or using a lookup.

Tips

  • Use an unambiguous display format such as 5 Jan 2026 when sharing a workbook across regions. A numeric date can be interpreted differently depending on whether the day or month comes first.
  • Keep one date per cell. If a cell contains a date and a note together, separate them into different columns before sorting or matching.
  • When comparing dates that include times, decide whether the time should matter. A date entered at midnight and a timestamp later that day are different underlying numbers, even if both cells are formatted to show only the date.
  • For a repeatable table, format the date column before entering or importing more records. A consistent format makes the sheet easier to scan, but still verify that imported values are real dates.
  • For a large sheet, use filters or a formatted Excel table to inspect dates and records. Keep the date column’s header clear, such as Transaction Date, so it is easy to select the right sort field.

Troubleshooting

The alignment buttons do not move the date. Confirm that you selected the intended cells and are using the horizontal alignment controls in Home > Alignment. Check whether the cell has an indent setting or a special format. Open Format Cells with Ctrl+1 and inspect the Alignment tab; on Mac, use Command+1. Also make sure the sheet is not protected in a way that prevents formatting changes.

The dates stay on the left and do not sort correctly. They may be text rather than date serials. Try the General-format check described above. If the values remain text, use Text to Columns with the correct date order, or use DATEVALUE on a consistent text format. If conversion returns an error, check for extra spaces, unusual separators, or a regional date order that Excel does not recognize.

The dates change from day/month to month/day. This is usually a display-format or regional interpretation issue. Open Format Cells and choose an explicit date format, and when converting imported text choose the original order in Text to Columns. Do not use a format to disguise a conversion made with the wrong order; verify a few known dates against the source.

Some lookup results are blank even though the dates look the same. Confirm that the lookup range contains real dates and that the date in the comparison list has the same underlying value. Check for hidden time portions, text values, or duplicates. If the source includes times but the comparison list has dates only, create a separate date-only key consistently in both lists before matching. Also verify that the lookup and return ranges cover all the intended rows.

Sorting makes the records appear jumbled. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if the workbook is still in the same editing session. Repeat the sort and choose Expand the selection. If you have already made other changes, restore the original data from a saved copy or version history rather than trying to reconstruct the pairing by eye.

Frequently Asked Questions

How do I center dates in Excel?
Select the cells, then choose Home > Center in the Alignment group. This centers the displayed contents but does not change the date value. Use Format Cells if you also need to change the date style.

Why are some dates left-aligned and others right-aligned?
Excel commonly aligns text to the left and numbers, including real dates, to the right by default. Mixed alignment can indicate that some entries are stored as text. Check a sample using General format and convert text dates before sorting or matching.

Can I align dates across two worksheets?
Yes. Standardize both date columns, then sort each complete table by its date field. If the lists have missing dates or different lengths, use a shared date list and a lookup such as XLOOKUP rather than relying on row numbers.

How can I make dates line up when some days are missing?
Create or use a master list of dates, then match each dataset to that list by date. A lookup can return a blank for dates that are absent, leaving both results on the same date row without shifting later records.

Does changing the date format change the date itself?
No. A number format changes how Excel displays a valid date, not the stored date serial. To correct a date that was imported as text or interpreted with the wrong day/month order, convert the value and verify it against the original source.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.