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)
| Method | Creates a real Excel date | Setup complexity | Main advantage | Main limitation | Recommended use |
|---|---|---|---|---|---|
| SUBSTITUTE Formula | Not necessarily; result may remain text | Simple | Fastest way to change . to / | May still leave the result as text | When you only need to change the separator |
| Kutools for Excel | Yes | Very simple | Fast bulk conversion with genuine date values | Requires Kutools for Excel | Large ranges or frequent nonstandard-date conversion |
| Date Formula | Yes | Moderate | Built into Excel and produces real dates | Requires formula setup and date formatting | Smaller datasets needing formula-based control |
| VBA Macro | Yes | More advanced | Reusable and suitable for automation | Cannot be undone with Ctrl+Z; VBA knowledge required | Repeated automated conversions |
| Text to Columns | Yes, after recombining the split values | More steps required | Built into Excel and gives direct control over day, month, and year | Requires splitting and recombining the date parts | Consistent single-column data when you prefer built-in Excel tools |
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 installingKutools 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.
Conclusion
Converting dates such as DD.MM.YYYY into real Excel dates can be done in several ways, depending on whether you only need to change the separator or create genuine date values for calculations.
Use the SUBSTITUTE formula when you only need to replace dots with slashes. For real Excel dates, the Date Formula works well for smaller datasets, while VBA is more suitable for repeated automation. Text to Columns provides a built-in way to split and rebuild the date components, and Kutools for Excel offers a faster batch-conversion option for large ranges or frequently processed nonstandard dates.
Choose the method based on your data size, whether you need automation, and whether the final result must be a true Excel date value.
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