Excel Date Time Formatting: From Basics to Real Fixes

A colleague of mine (let's call him Marcus) spent every Friday afternoon reformatting dates by hand. His accounting system exported timestamps as text, he'd paste them into his report, and then spend three hours typing the same dates over again in a format Excel actually understood. Three hours. Every week. For two years. Once I showed him a single Power Query step, that disappeared to about thirty seconds. The whole category of excel date time formatting problems is like that: invisible, painful, and completely fixable once you know what's actually happening under the hood.

This guide walks through the exact steps: applying built-in formats, building custom ones, and converting imported dates that Excel refuses to treat as dates. If you're newer to how Excel handles data generally, the Excel for Beginners starter guide is worth reading first. But if dates are your specific problem right now, keep going.

---

What You'll Be Able to Do, and Why Excel Date Time Formatting Trips So Many People Up

By the end of this, you'll be able to apply any date or time format you need, build custom display strings, and rescue dates that imported as plain text. That last one is where most real-world pain lives.

Why Dates Look Like Numbers (The Date Serial Number Explained)

Every date in Microsoft Excel is stored as a plain integer called a date serial number. January 1, 1900 = 1. January 1, 2026 = 45,658. The date format you apply is purely cosmetic: it tells Excel how to display that number, not what to store. In other words, the cell contains 45,658. The format just makes it show as "January 1, 2026."

This is the one concept most tutorials mention and move past. Don't. Once it clicks, you'll never be confused by a date cell suddenly showing a five-digit number again. It just means the number format got cleared, and the underlying date and time values are completely intact.

---

Step 1: Open the Format Cells Dialog and Apply a Built-In Date or Time Format

Now that you know the serial number is the real value, formatting becomes a display decision, not a data change. Here's how to make it.

How to Get to the Format Cells Dialog Fast

1. Select the cell or range containing your dates.
2. Press Ctrl+1 on Windows or Cmd+1 on Excel for Mac. The Format Cells dialog opens immediately.
3. Click the Number tab if it isn't already active.

You can also right-click and choose Format Cells, but the keyboard shortcut is faster. I use Ctrl+1 so habitually at this point that it's essentially muscle memory.

Choosing a Built-In Date or Time Format Code

1. In the Category list, select Date or Time.
2. Pick a format from the Type list. You'll see a live preview above it.
3. Click OK.

The stored value doesn't change. At all. You're telling Excel how to render the number, nothing more. This is worth repeating because it's the most common fear: people avoid reformatting dates because they think they'll break something. You won't. The number format is a lens, not an editor. This same principle applies to formatting numbers like currency and percentages: the value stays put.

---

Step 2: Build a Custom Date Format to Display Exactly What You Need

Built-in formats cover the basics. When you need dd/mm/yyyy or a timestamp like 14-Mar-2026 09:30, you'll build it yourself. It's less complicated than it looks.

Custom Format Codes for Dates and Times

Back in the Format Cells dialog, select Custom from the Category list. Type your format string directly into the Type field. The tokens work like this: d = day without leading zero, dd = day with leading zero, mmm = abbreviated month name (Jan, Feb…), mmmm = full month name, yyyy = four-digit year. For time: h = hours, mm = minutes, ss = seconds, AM/PM switches to 12-hour display.

So Excel date format dd mm yyyy looks like this in the Type field: dd/mm/yyyy. For a 12-hour timestamp: h:mm:ss AM/PM. The preview updates as you type. Watch it.

Combining Date and Time in One Cell

Excel stores time as the decimal portion of the serial number. Noon on January 1, 2026 is stored as 45658.5. So you can combine date and time in one cell with a format like dd/mm/yyyy h:mm, no extra column needed.

Don't use the TEXT function in the middle of a calculation chain. TEXT converts a date to a string, and any downstream formula expecting a real date will throw a #VALUE! error. TEXT is a finishing tool: use it when the output is going straight into a report or label, never as an intermediate step.

---

Step 3: Fix Dates That Imported as Text Using Power Query or DATEVALUE

Custom formats handle display. This step handles the situation where no format code works, because what you have isn't a date at all.

Dates imported from CSV files, accounting systems, or web exports frequently arrive as text that looks exactly like a real date. The dead giveaway: text-stored dates left-align in the cell by default. Real dates right-align. I learned this the way most people do, by spending twenty minutes troubleshooting a DATEDIF formula that kept failing before noticing the alignment.

The deeper problem is that these dates are invisible fakes. They pass a visual inspection completely. The cell appears formatted correctly. The value looks right. But formulas fail silently, or not so silently, producing #VALUE! errors that seem to come from nowhere. I had another colleague, David, who fought a trailing-spaces problem that worked exactly the same way. The data looked clean. It wasn't.

To fix text-stored dates in Power Query (available in Microsoft 365 and Excel 2016+): load your data into Power Query, right-click the date column header, choose Change Type → Date. Power Query detects and converts. For a quick in-cell fix, =DATEVALUE(A1) converts a text date to a real serial number. Then apply your format as normal. DATEVALUE has been available since Excel 2010, so version compatibility isn't a concern here.

Cross-platform gotcha: Windows Excel defaults to the 1900 date system; Excel for Mac historically defaulted to 1904. If you're sharing files between both, a date mismatch of exactly 1,462 days is usually this problem. Check under File → Options → Advanced → "Use 1904 date system" and make sure both files match.

For broader context on how Excel handles different data types in cells, the guide to Excel data types covers this well.

---

Common Excel Date Time Formatting Mistakes and How to Correct Them Fast

Three problems come up constantly.

Dates displaying as raw serial numbers means the cell has no date format applied, or it got pasted as a number. Fix: select the cell, Ctrl+1, pick a date format. The value is fine; it just needs a format telling Excel how to render it.

Custom formats accidentally changing stored values is a persistent myth. They don't. The custom date format is display-only. If you see unexpected values after formatting, the issue is somewhere in the formula chain, not the format itself.

Milliseconds not showing is the one that surprises people. Excel's standard time format codes don't support sub-second display natively. The workaround is a separate column: multiply the decimal time value by 86,400,000 to get milliseconds, then format that column as a number. Not elegant, but it works. This is one of those edge cases worth knowing exists before you spend an hour assuming you've made a mistake.

Always test your date formulas against blank cells. A blank date cell feeding into a formula will often produce a #VALUE! or #NUM! error in a template that looked perfect during testing. It's the kind of thing that breaks a senior management presentation at exactly the wrong moment. Trust me on that one.

For more on structuring clean, error-resistant data entry in Excel, the data entry and formatting guide covers the habits that prevent these problems upstream.

If you take one thing from this article: learn the serial number. Every date confusion (five-digit displays, format codes that seem wrong, imported dates that won't calculate) traces back to not knowing that Excel sees dates as integers. Understand that, and the rest of this gets easier fast.

---