Custom Date Format

In our previous post, we saw couple of things about dates & times. There are formats available to display a number as a date / time. However, If you couldn't find the date or time format you are looking for in the built in list, you can create one of your own. Read on to know more about custom date and time formats.

To Create Custom Date And / Or Time Format

Press Ctrl+1 > Custom tab > Click one of the date format code available under Type or type your own code as explained in the next section.

In the Custom type, you can see some more date/time formats at your disposal (you have to scroll down to see them). See if one of them is what you want. If you find the one you wanted, click it and click OK. But if you want more, why wait, read on.

To get the desired format you want, you have to write the code that represents it in Type: box. Don’t worry; this code is not complex like VBA code. See below to know what this code is.

Understand the Format Code

Format code can be built using characters specified to represent each category of displayed values. For example: alphabet h denotes hours, s denotes seconds etc. Below is the list of all characters used for date and time.

• You can combine one or more code items to get the format you want.
• You can use characters like space, : (colon), / (forward slash), - (hyphen) to separate days, months, years etc.
• Notice that m is used both for months and minutes. If you place m right side of h, it is treated as minutes. Otherwise, it is to represent months.
• If you want a particular text to be displayed, you can enclose your text in double quotes (Ex: “Total is: ”)

Let’s build a couple of custom formats using above info.

Display as Monday, 24 March 2014 03:51 PM

Let’s divide what we need into parts. We need:

• A complete day in text i.e. Monday
• A comma
• A Space
• Date in 2 digit number i.e. 24
• A Space
• Complete Month in text i.e. March
• A Space
• Year in 4 digits i.e. 2014
• A Space
• Hours in 2 digits i.e. 03
• A colon
• Minutes in 2 digits i.e. 51
• A Space
• Time of the day (AM or PM)

Looking at the table above, you can get code characters you need for each one of above items. Let’s summarize them.

We use a space character to get space and colon character for a colon. So our final code will be

dddd, dd mmmm yyyy hh:mm AM/PM

Try it, copy above line in to the Type: field and click OK. Enter a date and see how it is displayed. Notice the formula bar too.

Display as The Date Is: 24 March 2014

Let’s see what we need to display above format.

• A text i.e. The Date Is:
• 2 digit date i.e. 24
• Full month i.e. March
• Four digit year i.e. 2014

From the table above, we can deduce the code as below.

"The Date Is" : dd mmmm yyyy

Copy above line in to the Type: field and click OK. Enter a date and see how it is displayed, notice the formula bar.

You can see the text you entered on the face of excel. But formula bar doesn't show it. You can use this cell in any formula just like a normal number.

There are number of date and time formats you can build based on your needs. Above is the required information to give you a head start. Try more combinations for yourself. Happy learning!!!