Paste Dates as Text in Excel Without Changing Them

How-to basics · Updated November 21, 2026

To paste dates into Excel as text, first convert them to text with a helper formula, copy the results, then use Home > Paste > Values in the destination. This prevents Excel from treating the pasted entries as date values that can be reformatted or used in date calculations. You will need a workbook with the dates you want to copy and a free column for the formula; the steps below are for Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel workbook containing the dates, or a clear idea of the date strings you want to paste.
  • A spare worksheet column if you are converting dates that are already in Excel.
  • The date format you want the text to show, such as 2026-04-07 or 07-Apr-2026. Choose it before converting, because text does not automatically change its display format later.

Step-by-Step

  1. Check whether the source cells contain real dates. Click a date cell and look at the formula bar. Excel stores real dates as serial numbers and displays them using a number format. Changing a cell’s format to Text after a real date is already entered does not reliably convert its value into text. If you need a text result, use the conversion below rather than relying on the cell format alone.
  2. Choose a consistent text format. Decide how each date should appear in the destination. An unambiguous option is a four-digit year followed by the month and day, such as 2026-04-07. A format such as 04/07/2026 can mean different dates in different regional settings, so use it only when you know the recipient’s expected month-day order.
  3. Set up a helper column beside the dates. For example, if your dates are in A2:A20, put a heading such as Text date in B1. Select B2 and enter this formula: =TEXT(A2,"yyyy-mm-dd"). This assumes A2 contains a valid Excel date; if A2 is April 7, 2026, the result is the text string 2026-04-07. The TEXT function turns the date value into text using the format code between the quotation marks.
  4. Fill the formula down the list. Select B2 and drag its fill handle down alongside the source dates, or copy B2 and paste it into the remaining cells in column B. Each row should refer to the date in the same row. Check a few results against column A, especially near the bottom of a long list. If your dates are in another column or start on another row, adjust the cell reference in the formula accordingly.
  5. Copy the text results. Select the completed helper cells, such as B2:B20, and press Ctrl+C. If you do not want the heading copied, leave B1 out of the selection. At this point the cells contain formula results, so do not paste them as ordinary formulas into a different workbook if you need independent, fixed text.
  6. Paste the results as values. Select the destination cell, then choose Home > Paste > Values. The destination cells now contain the text strings rather than formulas linked to the source dates. To use a keyboard route on Windows, press Ctrl+Alt+V, choose Values in the Paste Special dialog, and confirm. On Mac, use Home > Paste > Paste Special and choose Values; keyboard shortcuts can differ by Excel version and keyboard settings.
  7. Verify that the pasted entries are text. Select one of the destination cells and inspect the formula bar. A pasted result should appear as the string you formatted, such as 2026-04-07, and not as a formula beginning with an equals sign. You can also test a cell with =ISTEXT(D2) if the pasted text is in D2; TRUE confirms it is text. If you need the text to replace the original dates, paste the values over the original cells only after checking the results, and keep a backup or copy first.

If the dates are not yet in Excel and you are bringing them in from a CSV or text file, you can often set the column type during import instead. In desktop Excel for Windows, choose Data > From Text/CSV, select the file, and use Transform Data to open Power Query. Select the date column, choose Data Type > Text, then choose Home > Close & Load. Check the preview before loading: if Excel or Power Query has already interpreted the entries as dates, confirm the text values retain the intended order and wording. For a small list, the helper-formula method is usually easier to inspect.

Common Mistakes

  • Formatting the cells as Text after entering dates. Applying Home > Number Format > Text changes the format setting, but it does not necessarily change existing date serial values into text. Convert existing dates with TEXT, then paste the results as values.
  • Using ordinary Paste and expecting text. A normal paste can carry formulas, number formats, and other source-cell properties. Copying the helper column and choosing Values makes the conversion explicit and avoids leaving formulas that depend on the source range.
  • Choosing a format that changes the intended date. A value such as 03/04/2026 is ambiguous across regions. Use a format such as yyyy-mm-dd when possible, or confirm which order the receiving system expects before converting.
  • Deleting the original dates too soon. A formula-based helper column depends on the source cells until you paste its results as values. Check the output and make sure it is fixed text before removing or overwriting the source.
  • Assuming text dates behave like date values. Text strings are useful for exports, labels, and systems that require literal date characters, but Excel will not treat them as ordinary dates for sorting chronologically, date arithmetic, or date-based calculations. Convert them back to dates if you need those features.

Tips

  • Pick a durable format for sharing. The format yyyy-mm-dd is easy to read and usually avoids confusion between month-first and day-first regional settings. If a system requires another pattern, use its exact specification instead.
  • Include times when they matter. If the source cells contain date-and-time values, choose a format that includes both parts. For example, with the date-and-time value in A2, =TEXT(A2,"yyyy-mm-dd hh:mm") returns a string like 2026-04-07 14:30. The displayed time depends on the underlying value and the format you choose.
  • Keep a separate original column. Retaining the source dates until the destination text has been checked makes it easy to correct a format choice without reimporting or retyping data.
  • Use a formula when the format must be exact. A helper formula makes every result follow the same pattern, even if the source cells have mixed display formats. Review the chosen pattern for dates near the start of a month or year, where an incorrect order is easier to miss.
  • Remember that text does not update like a date. If you change the original date after creating a text result, the formula result updates while the formula remains. Once you paste values, the text becomes fixed and will not change when the source date changes.

Troubleshooting

  • The result shows a number or an unexpected date. Check that the source cell actually contains a valid date, and review the format code in the TEXT formula. The formula uses yyyy for a four-digit year, mm for a two-digit month, and dd for a two-digit day. If the source is already text rather than a real date, the formula may not interpret it as intended; confirm the source’s regional date order before converting it.
  • The formula appears in the cell instead of a result. Make sure the entry begins with an equals sign and that the cell is not being treated as text. If the cell was formatted as Text before you entered the formula, change it to General, re-enter the formula, and press Enter.
  • The pasted value is still a formula. Undo if appropriate, copy the helper results again, and use Home > Paste > Values at the destination. Check the formula bar afterward. If it begins with an equals sign, the formula rather than its result was pasted.
  • The day and month appear reversed. This can happen when a source date string is interpreted under a different regional setting. Do not guess based on a value such as 05/06/2026. Check the source convention first, then convert it to an unambiguous text pattern.
  • Some cells return an error or a different-looking result. Look for blanks, text entries mixed with dates, or invalid source values in those rows. Correct or handle those entries separately, then fill the formula down again. If you imported the data, review the import preview and set the column to Text before loading when literal strings are required.

Frequently Asked Questions

Can I paste dates as text without using a formula?
Yes, if you are typing a date yourself, start the entry with an apostrophe, for example '2026-04-07. Excel treats the entry as text and does not display the apostrophe in the cell. For many existing dates, a TEXT formula followed by Paste Values is more consistent.

Does changing the cell format to Text convert dates that are already there?
No, not reliably. Formatting controls how Excel displays a value; it does not by itself turn a stored date serial into a text string. Use a conversion method such as the helper formula, then paste the result as values.

Can I still sort dates that I pasted as text?
You can sort the entries, but a text sort may be alphabetical rather than chronological. A fixed year-month-day format often sorts in date order as text, but it is safer to keep real date values for calculations and chronological analysis.

Will the TEXT formula work in Excel for the web or on Mac?
TEXT is available in current Excel for Microsoft 365 on Windows, Mac, and the web. Ribbon labels and Paste Special shortcuts can vary, so use the equivalent Paste Values option in your version.

How do I keep the exact characters from a CSV file?
Import the file through Data > From Text/CSV and set the relevant column to Text in the Power Query editor before loading. Then inspect the loaded results for leading zeros, separators, and date order. If the characters have already been converted to date values, use a formula to create the text pattern you need.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.