30 September 2026

Convert Text to Dates in Excel with the Paste Special Add Zero Trick

Office-Watch.com Microsoft 365, Microsoft Excel, Microsoft Office for Mac, Office 2007, Office 2010, Office 2013, Office 2016, Office 2019, Office 2021 / Office LTSC, Office 2024, Office 365, Office for Mere Mortals

Dates imported or pasted into Excel often arrive as text — they look like dates but Excel can’t use them in formulas or sorting. One quick fix is the Paste Special Add Zero trick. It forces Excel to convert text into a real date value by adding zero, which changes the data type without changing the result. Here’s how it works and what to watch out for.

It’s a long-standing trick for converting text into values, not just dates but also numbers.

How the Add Zero Trick Works,

The operation adds, subtracts, multiplies, or divides the value in the destination cell with the pasted value. In this case, all the destination cells are blank so zero is added.

Note: the Excel ‘plus zero’ conversion trick works with Paste Special | Add to blank cells or in a formula as ‘+0’ (e.g. =A2+0 ) but does not work with the Sum() function. SUM() already treats text-that-looks-like-numbers differently; it ignores text strings in cell references, so adding a text cell within SUM() won’t force conversion.

However, it has the same problems as using DateValue() or Value() for date conversion. We mention it for the sake of completeness, rather than as a good alternative.

Step-by-Step Instructions

Select the text cells and copy to clipboard with Ctrl + C
Place the cursor in the first cell of the pasting destination
Choose Home | Clipboard | Paste | Paste Special then Operation: Add.

Block Special dialog box in Excel with Add option selected

Here’s the result with the results as numbers:

Block showing text converted to date serial numbers using add zero

Change the cell formatting of column B to a date format.

Block cells reformatted from text to date format after Paste Special with errors.

Date Conversion Pitfalls

As you can see, there are the conversion problems as DateValue() and Value(), the month and day aren’t always converted correctly. For example, 8/18 becomes 1 Aug 2018, not 18 August and 7/11 becomes 7 Nov, not 11 July.

Excel interprets ambiguous date text based on the system’s regional/locale settings, which can lead to month/day swaps.

Dig Deeper: More Excel Date Conversion Technique

This Paste Special trick is just one approach to a common and frustrating problem. For a broader look at the method covered here, including more examples, see Text to Excel Date conversion by adding Zero with Paste Special.

When your text includes the day, month and year as separate pieces — like “15 March 2024” — you’ll need a different formula-based approach. Simple text with day, month and year to Excel date conversion walks through that step by step.

If your data only has a month and year with no day at all, the conversion gets trickier. Converting Text with month and year into Excel dates shows how to handle that specific scenario without losing accuracy.