FREE CHEAT SHEET
All the key steps in a printable cheat sheet
PLUS, GET OUR 30 EXCEL POWER TIPS FOR FREE
Enter your details for immediate access
Have you ever wondered how to display dates in a way other than the standard dd/mm/yyyy format?
In addition to the standard date formats, you can create your own custom date formats to fit the purpose exactly.
When you think about the different ways you can display a date, there’s quite a few standard options. Here’s a sample:
The safest way to type a date is to use slashes (/) as separators between day, month and year. When you do this, Excel will always convert to the default date format of 02/12/2014.
If you separate with dots (.), Excel doesn’t know what to do. Excel’s primary purpose is to crunch numbers and it thinks you have entered 2 decimal points
Excel converts your date to text. Which is okay if you simply want to display the date, but if you need it for a calculation, you’re out of luck.
That really comes down to the context in which the date is being used.
The choice is yours.
We’ll come on to how to create a custom date format in a moment but first here’s how to …
You create a custom date format by using a system of placeholders.
There are 3 primary date placeholders -
but the magic happens in how you use them.
Here’s some examples of date formats that you can create that do not appear in the regular list. All of these example use the date 2 Dec 2014.
Any of the custom date formats mentioned previously can be used within a TEXT formula to convert an existing date (in whatever existing format) to your desired format.
Say, cell A1 contains the date 15/06/2017.
In a new cell type ...
=TEXT(A1, "dddd, dd mmmm yyyy")
This results in Thursday, 15 June 2017.
You can replace the placeholders within the quotes with anything you want.
Did this clear something up for you? If so, please share on your socials and/or post a brief comment below.
Likewise, if something didn't make sense or you have a question, pop it into the comments below. I'll be waiting ...
Cheers. Here's to your learning and success. Enjoy the rest of your day.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.