Fix Excel Paste Not Keeping Destination Date Formatting
Question details
The user wants pasted dates to use the destination cells' date format in Excel.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Pasting dates such as 23 Mar 2017 into cells with an existing date format.
- Observed behavior
- The pasted entries do not appear in the destination date format; the source values may be stored as text.
Check the destination cells' date format under Home > Number Format. In an empty cell, enter =ISNUMBER(A2), replacing A2 with a source cell, to check whether the date is stored as a number.
Paste numeric dates as values into formatted cells
Use this method when the source dates are real Excel dates and you want to retain the destination formatting.
Select the destination cells, open Home > Number Format > More Number Formats, and choose the required Date or Custom format.
Select the source cells and choose Home > Copy.
Select the first destination cell, then choose Home > Paste > Values. Check that the dates display in the destination format.
If the formatting still differs, choose File > New and repeat the copy-and-paste test in a new worksheet.

Convert text dates before pasting
Use this method when ISNUMBER returns FALSE for a source date that is stored as text.
Work with date-formatted spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open existing Excel files and continue editing dates without rebuilding your workbook.

Frequently Asked Questions
Why does changing the date format not change my pasted dates?
The pasted entries may be text rather than numeric date values. Test a source cell with =ISNUMBER(A2); if it returns FALSE, convert the text before applying a date format.
How do I paste dates without copying the source formatting?
Copy the source cells, select the destination, and choose Home > Paste > Values. The destination cells should already have the date format you want.
What should I do if DATEVALUE returns an error?
Check that the source text is a recognizable date and review your system's regional date settings. DATEVALUE may not recognize a date written in a different regional format.
How can I tell whether the issue is specific to my workbook?
Choose File > New in Excel, format a destination cell as a date, and repeat the paste test in the new worksheet.




