Excel Number Formatting for Dates

In the big world we live in, there are many different ways to write the date. If it’s the first of June, you might write it as June 1, 2024. You may write it as 6/1/2024. Or even 1/6/2024.

As a practitioner of data analytics, my preferred way to write June 1, 2024 is 2024-06-01. The general way to describe this number format is yyyy-mm-dd. Why I prefer this style is that it naturally sorts. It goes beyond Excel. If you have documents, pictures, any file or folder on your device, you prefix it using the yyyy-mm-dd convention for the date, then sort it alphabetically, the items show up in chronological order.

This is a valid number format in Excel and will work*. The reason for the * is when you work with international colleagues. If they have a foreign language Windows or Excel, all bets are off with yyyy. I’ve had screen-share sessions with colleagues and noticed that for some (not all) their Excel showed yyyy-06-01. After a bit of research, I discovered the proper way to show year in an internationally-friendly way is to use e in place of yyyy.

Therefore, the correct number formatting string for year-month-day is actually:

What’s amusing is the Excel documentation on this topic doesn’t cover this, instructing the use of yyyy. Now you know!

Scroll to Top