Excel Custom Date Formatting

excel custom dateMost of us at some point have tried to find a particular number format, but we weren’t able to find one that satisfies our needs. There will be another blog to follow this one that specifically deals with time, this one is just about dates.

How-Toexcel custom date how to

In order to create a custom number format, you’ll need to access the Format Cells window. Once there, choose the “Custom” option from the left hand side. Under the word “Type” you’ll be typing in your custom number format.

How to Type the Format

Customizing dates is easy! We just have to know what the simple code is. Keep in mind, you don’t NEED the day, month, and year. You can choose to display just the month, or the year.

Formatting Characters


Put in the following codes on the left to achieve the look on the right

YYYY                   2016

YY                          16


Code on the left; result on the right

MMMM           January

MMM                Jan

MM                     01

M                          1


Code on the left; result on the right

DDDD                Friday

DDD                    Fri

DD                        01


Examples in Practice:

Want your employees’ birth years to be hidden? Try

MMM DD      to see           how to use custom excel

Use the dashed, commas and slashes where ever you’d like them.

DDDD, MMM DD       will give you       how to use custom date formating

The TEXT function

The above method allows us to see something displayed without excel actually considering it a different value. However if your objective is to actually have the date value be retrievable by another function, then you’ll want to use the TEXT function. It has the same formatting rules conveniently.

custom date example how to  This will display asexcel how to

The benefit in this case is that some other function is dependent on this cell to say “January”, it will be able to retrieve that text.