How to convert DD.MM.YYYY to date format (mM/DD/YYYY) in Excel?
When working with Excel, you may encounter dates entered in the dd.mm.yyyy format due to varying regional habits or personal preferences. However, Excel does not automatically recognize dd.mm.yyyy (for example, 23.02.2024) as a valid date format, which can lead to issues with sorting, filtering, or date calculations. To ensure full compatibility and convenient data processing, it's important to convert these text-based date strings into the standard Excel date format, such as mm/dd/yyyy.
Below, you'll find several effective solutions to tackle this problem in different ways—ranging from using formulas, built-in Excel features, to VBA code. Each method comes with practical steps, precautions, and suggestions for troubleshooting common issues.
Convert dd.mm.yyyy to dd/mm/yyyy with formula
Convert mm.dd.yyyy to mm/dd/yyyy with Kutools for Excel
Convert dd.mm.yyyy to mm/dd/yyyy with formula
Convert dd.mm.yyyy to standard date using VBA macro
Convert dd.mm.yyyy using Text to Columns (Excel built-in)
Convert DD.MM.YYYY to DD/MM/YYYY with formula
In some cases, you may only need to replace the dots in dd.mm.yyyy with slashes to get dd/mm/yyyy. This is helpful when you want the separator to match the regional settings, but note that Excel may still treat the result as a text string, not a real date value.
To perform this conversion:
1. Suppose your original date is in cell A6. Select a blank cell next to it—such as B6—and enter the following formula:
=SUBSTITUTE(A6,".","/")2. Press Enter, then drag the fill handle down to apply the formula for other dates as needed.
Tip: In this formula, A6 refers to the cell with the original date. Adjust the cell reference as necessary for your data range.
While this method is simple, remember that the result is still text rather than a recognized date value. If your subsequent operations require real dates (for calculation, filtering, etc.), please refer to the following formula and VBA solutions.
Convert MM.DD.YYYY to MM/DD/YYYY with Kutools for Excel
For dates in the format mm.dd.yyyy, Kutools for Excel offers a practical feature called Convert to Date. This tool enables you to quickly convert multiple non-standard date formats to standard date values in bulk—especially useful if you frequently process imported or combined data from various sources.
After free downloading and installing Kutools for Excel, follow these steps:
1. Select the cells containing the dates to be converted. Then go to Kutools > Content > Convert to Date.
2. The selected cells will be automatically converted to valid Excel date values. You can choose different date display formats (Short Date, Long Date, etc.) from the Number Format dropdown in the Excel Home tab for enhanced visualization.
Tip: If a value is not recognized as a valid date, the original data will remain unchanged, which helps avoid accidental data loss.
This method is especially efficient for large data ranges and ensures the output is genuine date values for immediate use in calculations and filtering. Pros include bulk processing and easy conversion, while a possible drawback is that it requires installing the Kutools add-in.
Convert DD.MM.YYYY to MM/DD/YYYY with formula
To further transform dates from dd.mm.yyyy to the standard mm/dd/yyyy format and ensure Excel recognizes the result as a true date, you can use the following formula. This method is suitable if your regional date format does not recognize the slash-separated result from a simple SUBSTITUTE function as a date.
1. Assume the original date is in A6. In the adjacent cell, for example B6, enter this formula:
=(MID(A6,4,2)&"/"&LEFT(A6,2)&"/"&RIGHT(A6,2))+02. Press Enter, then drag the formula down as necessary.
3. The results may initially appear as serial numbers (such as45457). To view them as dates, select these cells, go to Home > Number Format, and choose Short Date.
Now, your dd.mm.yyyy formatted text is converted into Excel-recognized dates in mm/dd/yyyy format.
Tips: To copy the formula down multiple rows, select the first cell with the formula, copy (Ctrl+C), then select other target cells and paste (Ctrl+V)
VBA Code – Convert dd.mm.yyyy strings to real date values across a range
For advanced users or those dealing with large amounts of data in custom formats, automating the conversion with a VBA macro can be time-saving and efficient. This method directly transforms text-based dd.mm.yyyy dates into real Excel dates within your chosen range.
Advantages include batch processing and flexibility in choosing any column or range. However, use caution—VBA macros cannot be undone with Ctrl+Z. Be sure to back up your data before running the code.
1. Click Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module, and paste the following code into the module window:
Sub ConvertDDMMYYYYDotToDate()
Dim cell As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For Each cell In WorkRng
If cell.Value Like "??.??.????" Then
cell.Value = DateSerial(Right(cell.Value, 4), Mid(cell.Value, 4, 2), Left(cell.Value, 2))
cell.NumberFormat = "mm/dd/yyyy"
End If
Next
Application.ScreenUpdating = True
End Sub2. Then, press F5 key to run this code, in the prompt box, select the range containing your dd.mm.yyyy dates, and then, click OK.
Notes and Tips:
- If you get an error or nothing happens, check your selection and ensure the format exactly matches dd.mm.yyyy.
- You can adjust the cell.Value Like "??.??.????" pattern if your data has variable digit length.
- This macro cannot be reversed easily—always save a copy of your data first.
- The converted cells will immediately be recognized by Excel as real date values.
This VBA solution is ideal for users comfortable with basic macro operations and who need quick, accurate, and repeatable conversion on large datasets.
Other Built-in Excel Methods – Use Text to Columns
Another practical approach is to use Excel’s built-in Text to Columns feature. This method is most suitable when your date data is consistent and in a single column.
1. Select the column or cells containing your dd.mm.yyyy dates.
2. Go to Data > Text to Columns.
3. In the wizard, choose Delimited, then click Next.
4. Check only Other for delimiters, and type a dot (.) in the box.
5. Click Next. In the next step, set the Column Data Format for Day, Month, and Year columns as General or Text as appropriate.
6. Complete the wizard to split data into three columns (Day, Month, Year).
7. In a new column, recombine day, month, and year into a date using the formula:
=DATE(C1, B1, A1)Assuming columns A, B, and C now represent Day, Month, and Year, respectively. Apply the formula and drag it down as needed.
These solutions offer flexible options for converting dd.mm.yyyy and similar date formats into recognized Excel dates, whether you prefer formulas, visual tools, or automation. Always double-check your data before and after transformation, and consider saving a backup, especially when using macros or making batch adjustments.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in