Yes, you can remove date formatting in Excel with Format Cells → General. But if you only do that, your cell still holds a date serial number—and will behave like a date the second you use it in a formula or paste elsewhere.
The Problem
You copy a report from HR and paste it into Excel. Column C shows '3/15/2024', but when you type =C2+1, it returns '3/16/2024'. You didn’t ask for that. You just wanted the text '3/15/2024'—no arithmetic, no auto-conversion, no calendar logic.
This isn’t rare. It’s baked into Excel’s DNA: any value that looks like a date gets interpreted as one unless you intervene *before* Excel decides for you. And once it’s stored as a serial number (e.g., 45366 for March 15, 2024), changing the display format doesn’t change its identity.
| Employee | Department | Start Date | Salary |
|---|---|---|---|
| Sarah Chen | Finance | 3/15/2024 | $82,500 |
| Diego Mora | Sales | 7/2/2023 | $64,900 |
| Aisha Patel | Marketing | 11/30/2024 | $71,200 |
| Kenji Tanaka | Engineering | 1/8/2024 | $95,600 |
| Maya Dubois | HR | 5/22/2023 | $68,300 |
| Rajiv Mehta | Legal | 9/1/2024 | $89,100 |
| Lena Schmidt | Operations | 12/14/2023 | $74,800 |
Look at column C. All those entries look like dates—but they’re likely stored as serial numbers. You can confirm this by selecting C2 and checking the formula bar: you’ll see '3/15/2024', but the actual value is 45366. That’s why typing =C2+1 gives you March 16. Worse? If you sort that column, Excel sorts by serial number—not by visual appearance. So November 30, 2024 (45260) sorts before January 8, 2024 (45287). Yes, really. (Trust me—I debugged this for three hours on a payroll reconciliation last April.)
The Solution
We need to convert those date values into true text—or at least into numbers that won’t auto-convert. There are two reliable paths, depending on your goal:
Path A: Keep the date as text (so '3/15/2024' stays exactly that)
Use =TEXT(C2,"mm/dd/yyyy") in a new column, then Paste Values over the original.
Path B: Strip formatting *and* prevent future date interpretation
Use Paste Special → Values + Text, or force text entry with an apostrophe—but only *before* Excel locks it in.
Here’s the cleanest method for existing data in C2:C8:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select C2:C8 | Range highlighted | — |
| 2 | Press Ctrl+1, go to Number tab → select 'Text', click OK |
Cells now show as text—but values remain serial numbers (no visible change yet) | Ctrl+1 |
| 3 | Type =TEXT(C2,"m/d/yyyy") in D2, drag down to D8 |
D2 shows '3/15/2024' as true text | — |
| 4 | Copy D2:D8 → right-click C2 → Paste Special → Values (or press Alt+E+S+V) | C2:C8 now contains pure text, no formulas, no serial numbers | Alt+E+S+V |
| 5 | Clear D2:D8 | Done. Sorting C2:C8 now treats entries as strings (alphabetical order) | — |
After Step 4, try sorting column C again. '11/30/2024' now appears *after* '9/1/2024'—because Excel sorts '11' before '9' in text mode. Exactly what you’d expect.
| Employee | Department | Start Date (text) | Salary |
|---|---|---|---|
| Sarah Chen | Finance | 3/15/2024 | $82,500 |
| Diego Mora | Sales | 7/2/2023 | $64,900 |
| Aisha Patel | Marketing | 11/30/2024 | $71,200 |
| Kenji Tanaka | Engineering | 1/8/2024 | $95,600 |
| Maya Dubois | HR | 5/22/2023 | $68,300 |
| Rajiv Mehta | Legal | 9/1/2024 | $89,100 |
| Lena Schmidt | Operations | 12/14/2023 | $74,800 |
Going Further
Sometimes you don’t want text—you want the raw number without date logic. Say you’re importing timestamps from a log file and need seconds since epoch. Or you're building a dashboard where '45366' means more than '3/15/2024'.
In those cases, skip TEXT(). Just use =C2 in D2 and Paste Values. That strips formatting but keeps the serial number as a plain number. No date behavior. No automatic reformatting—even if you later apply General format.
For bulk cleanup of mixed data (some dates, some text, some numbers), try this trick: Select the range, press Alt+H+F+J (Home → Fill → Justify). Excel splits content across columns using spaces or tabs as delimiters—but crucially, it converts everything to text *in place*. Then delete the extra columns. I’ve used this to rescue CSV imports where Excel auto-converted '1-2-3' to a date instead of treating it as a product code.
And here’s the counterintuitive tip: If you’re pasting dates from the web or email, don’t paste directly. First, paste into Notepad. Then copy from Notepad and paste into Excel using Paste Special → Text (Alt+E+S+T). This bypasses Excel’s auto-detection entirely. It’s slower—but saves hours of undoing damage later.
When NOT to Use This
Don’t remove date formatting if you plan to calculate durations, age, or fiscal quarters. Serial numbers exist for a reason—they let Excel do math. Converting '3/15/2024' to text kills that capability instantly.
Avoid this method on cells linked to other sheets or dashboards. If Sheet2!C5 feeds a chart on Sheet1, converting C5 to text may break formulas referencing it—especially array formulas or dynamic arrays that expect dates.
Also skip it if your source data has inconsistent separators—like 'Mar 15, 2024', '15-Mar-2024', and '20240315' in the same column. Those need Power Query or regex cleaning first. Trying to TEXT() them all with one format string will return #VALUE! errors.
One more warning: Never apply this to cells containing time values alone (e.g., '2:45 PM'). The serial number includes fractional days. =TEXT(C2,"h:mm AM/PM") works—but =TEXT(C2,"m/d/yyyy") returns '1/0/1900' because Excel’s day-zero is Jan 1, 1900. (Yes, even though that date never existed in Excel’s calendar. Don’t ask.)
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Format Cells dialog | Ctrl+1 |
Then navigate with Tab/Arrow keys |
| Paste Special → Values | Alt+E+S+V |
Works even if ribbon isn’t visible |
| Paste Special → Text | Alt+E+S+T |
Preserves exact character strings |
| Fill Justify (text-splitting) | Alt+H+F+J |
Great for messy delimited imports |
| Enter edit mode in active cell | F2 |
Then type apostrophe (') to force text |
