Excel Convert Date Dot Slash: Easy Fixes

To convert a date written with dots (e.g., 12.05.2024) to a slash format (12/05/2024) in Excel, use the SUBSTITUTE function: =SUBSTITUTE(A1,".","/"). This replaces every dot with a slash, giving you a text string that Excel may or may not recognize as a date; to ensure it becomes a real date, wrap it in DATEVALUE or use Find & Replace. This article explains the methods, why dots cause problems, and how to handle dates that Excel misreads.

Formula

The core formula is =DATEVALUE(SUBSTITUTE(A1,".","/")). It works in two steps:

• SUBSTITUTE(A1,”.”,”/”) – replaces all periods in the cell with forward slashes. If A1 contains 12.05.2024, it returns 12/05/2024 as text.
• DATEVALUE(…) – converts that text into a real Excel date serial number, which you can then format as a date.

If you only need the text to look like a slash date (e.g., for a report), you can skip DATEVALUE and just use =SUBSTITUTE(A1,".","/"). But be aware that the result is text, not a date, so sorting and date arithmetic won’t work properly.

Worked Example

Using the default value from the calculator block: 12.05.2024 in cell A1.

1. Apply =SUBSTITUTE(A1,".","/") → returns 12/05/2024 (text).
2. Wrap with DATEVALUE: =DATEVALUE(SUBSTITUTE(A1,".","/")) → returns the serial number for 12 May 2024 (or 5 December 2024, depending on your system’s date settings).
3. Format the cell as a date (e.g., DD/MM/YYYY or MM/DD/YYYY) to display it as 12/05/2024 or 05/12/2024.

The result in the calculator block shows the converted date as a serial number (e.g., 45424 for 12 May 2024), which is how Excel stores dates internally.

Why Dots Cause Problems in Excel

Excel’s default date separators are slashes (/) or hyphens (-). When you type or import a date like 12.05.2024, Excel treats it as text because the dot is not a recognized separator. This means:

• The cell is left-aligned (text) instead of right-aligned (date).
• Formulas like =A1+1 fail or return errors.
• Sorting by date puts text in alphabetical order, not chronological.
• Pivot tables and charts may not group by month/year correctly.

This is common when copying data from European sources where dots are used as date separators, or from legacy systems that export dates with periods.

How to Convert Dots to Slashes – Step by Step

There are two main methods: using Find & Replace (quick, but changes all dots) and using formulas (safer for selective conversion).

Method 1: Find & Replace (Fastest)

1. Select the range of cells containing dot dates.
2. Press Ctrl+H to open Find & Replace.
3. In “Find whatâ€�, type a period ..
4. In “Replace withâ€�, type a slash /.
5. Click “Replace All�.
6. If the cells don’t turn into dates automatically, select them, press Ctrl+1, and apply a date format.

Caution: This replaces every period in the selected cells, so if you have decimal numbers like 3.14 in the same range, they will become 3/14 – a potential mistake.

Method 2: Formula (Safer)

1. In a new column, enter =DATEVALUE(SUBSTITUTE(A1,".","/")).
2. Drag the fill handle down to apply to all rows.
3. Format the new column as a date.
4. Copy the new column, then use Paste Special > Values to replace the original text.

This method only affects cells where you apply the formula, leaving any decimal numbers untouched.

Common Problems and Misconceptions

One frequent issue is that after using SUBSTITUTE, the result still looks like a date but is actually text. You can test this by checking the cell alignment or using =ISNUMBER(A1) – it will return FALSE for text.

Another problem: the day and month order. If your system uses MM/DD/YYYY and your dot date is DD.MM.YYYY, Excel will interpret 12.05.2024 as December 5 instead of May 12. To avoid this, always specify the format when converting: use =DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2)) if you know the original is DD.MM.YYYY.

Also, if the dot date includes a time (e.g., 12.05.2024 14:30), SUBSTITUTE will replace the dot in the time too, causing errors. In that case, use a more complex formula or Text to Columns.

When to Use a Formula vs. Find & Replace

Choose based on your situation:

• Find & Replace – best for a one-off cleanup of a column that contains only dates, no decimals.
• Formula – best when you need to preserve the original data, or when the range includes numbers with periods.
• Power Query – if you’re importing the same file regularly, use Power Query to replace dots during import, which is repeatable and doesn’t alter your raw data.

For large datasets, Power Query (Get & Transform) is more efficient than dragging formulas down thousands of rows.

Alternatives: Using Text to Columns

Another built-in tool is Text to Columns:

1. Select the column with dot dates.
2. Go to Data > Text to Columns.
3. Choose “Delimited� and click Next.
4. Check “Otherâ€� and type a period . as the delimiter.
5. Click Next, then in the column data format, choose “Date� and select the order (DMY or MDY).
6. Click Finish.

This method directly converts the text into a date, but it splits the date into three columns if you don’t merge them back. You can use a formula like =DATE(C1,B1,A1) to recombine, but it’s more steps than the SUBSTITUTE approach.

Frequently Asked Questions

Why does my dot date not convert with SUBSTITUTE?

If the cell contains a trailing space or non-breaking space, SUBSTITUTE will still work, but DATEVALUE might fail because the text isn’t exactly “12/05/2024â€�. Try using =TRIM(A1) first, or check for hidden characters with the LEN function.

How do I convert dots to slashes without changing decimal numbers?

Use a formula that only applies to date-like strings, e.g., =IF(ISNUMBER(SEARCH(".",A1)), DATEVALUE(SUBSTITUTE(A1,".","/")), A1). This checks if the cell contains a dot, and only converts if it does. For decimals like 3.14, SEARCH returns a position, so it would still convert – a better check is to see if the string has two dots (for date) vs. one (for decimal), but that’s more complex.

Will the converted date be recognized as a real date?

Yes, if you use DATEVALUE, the result is a date serial number. You can verify by formatting the cell as a date and checking if it right-aligns. If you only use SUBSTITUTE, it remains text.

What if my date is in the format YYYY.MM.DD?

The same SUBSTITUTE formula works, but the resulting slash date will be in the order YYYY/MM/DD. Excel may not recognize that as a date in all regional settings. Use =DATE(LEFT(A1,4), MID(A1,6,2), RIGHT(A1,2)) to explicitly build the date.

Can I change Excel’s default date separator to a dot?

Yes, but it’s a system-wide setting. In Windows, go to Control Panel > Region > Additional settings and change the date separator to .. In Excel, this will make typing 12.05.2024 automatically become a date. However, this affects all programs on your system, so it’s rarely recommended.

Related

• Claude Code Project Context: Cut Token Waste
• Screen Flashing Windows 10: Solved? Quick Fixes
• Delete IIS Log Files in Windows 10: Easy & Safe

in t | Date with dots | - | 12.05.2024 | 0
out d | Converted date | - | DATEVALUE(SUBSTITUTE(t,".","/")) | 0