Text to Excel Date conversion by adding Zero with Paste Special

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


Another way to convert text to dates is by adding zero to the text.  This is a trick which forces Excel to convert the text into an Excel value so it can then add the zero.  Since adding zero doesn’t change the value, you end up with text converted to an Excel value.

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

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

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

text to excel date conversion by adding zero with paste special 24527 - Text to Excel Date conversion by adding Zero with Paste Special

Operation add/subtract/multiply/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’ (eg  =A2+0 ) but does NOT work with the Sum() function.

Here’s the result with the results as numbers:

text to excel date conversion by adding zero with paste special 24529 - Text to Excel Date conversion by adding Zero with Paste Special

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

img 5be94be00a130 - Text to Excel Date conversion by adding Zero with Paste Special

As you can see, there’s the same conversion problems as DateValue() and Value().

Converting text numbers to Excel values should be more accurate.


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.