Formatting Dates in Excel the better way using Custom Number Formatting – How To

Time is of prime importance and having dates in your data is most of the time essential bit of whole. However, making the dates look the way we want is often cumbersome in spreadsheets as sometimes it changes, flips on its own or it doesn’t work the way want it to. This definitely frustrates many and I was one of them. But soon I learnt its not the Excel that has any problem, its just my lack of knowledge to tame Excel the way I want it to work.

Have a look at the different formats that we might desire for the same date:

date formats example

Want to learn how to do this? That’s where my favourite feature Customer Number Formatting comes handy. Yes dates are considered numbers by Excel.

Using predetermined / built-in formats

Excel offers you great variety of preformatted options to display date. By default dates your shown in the following format: month/day/year. And these are all in numbers. To change the format to a certain predefined format, select the cell holding date and hit Ctrl+1. This will open format cell dialogue box with number tab active.

From the list on left select date and on the right you will get several options as show below:

date format 1

As you can see, it can suffice most of the needs of most of the users. But definitely not everyone. For example, What if you want the dates to show up like this:

September (09) 22 (Thursday), 2011

In this case predefined formats are not going to help. But we can still do it if we really know how to harness the power of Customer formatting in Excel. And this is exactly what this article is about.

Custom Date Formats with Customer Formatting

Write the the following date in any cell:

08/06/1986

Having the cell containing date selected hit Ctrl+1 to open the same format cell dialogue box. But this time from the left select custom. Now in the type field you can punch in any of the following codes and pay attention how it is affecting the results. You can see the effect on run time basis just above the type field also. Following animation shows:

date format 3

I have summarized how each of the code affects the date and what it shows. Have a a look at the following table:

DateFormat codeResultDescription
8/6/1986d6Just the day with no leading zero
8/6/1986dd06Just the day with leading zero
8/6/1986dddWedDay in short words
8/6/1986ddddWednesdayDay in full words
8/6/1986m8Just the month with no leading zero
8/6/1986mm08Just the month with leading zero
8/6/1986mmmAugMonth in short words
8/6/1986mmmmAugustMonth in full words
8/6/1986yy86Just the year with no leading century
8/6/1986yyyy1986Just the year with leading century

Once we understand how each of them works, we can workout our own formats the way we want. Just toss them up with dashes or obliques or periods or just the way you want. But lets try the one we wanted in the beginning which was:  September (09) 22 (Monday), 2014

Just insert this date in the desired cell and give the following format code:

mmmm (mm) dd (dddd), yyyy

Bonus Tip 1: Showing Month Initials

While going through predefined date formats, I saw a date format which was odd and was like this:

S-11

Later I found that you can show months as initials as well. For example the month of September can be displayed as “S” only. And for this you need to put the following code:

mmmmm

Yes five times m in the format code. And this is only for the month as I so far know and does not work for Days or Years portion of the date.

Bonus Tip 2: WHAT THE ####!

Just in case you format the date and you get this:

######

Then don’t worry you just have to expand the column to display it properly. Basically the column width is too tight to display the date with format selected that is why it resulted in hash-tags.

date format 2

More Examples

Following are some of the combinations I made using the above codes:

Original formatCodeDesired format
8/6/1986mmmm dd, yyyyAugust 06, 1986
8/6/1986“[“mmm”]” dd – yyyy[Aug] 06 – 1986
8/6/1986mm (dd) yy08 (06) 86
8/6/1986mmm, dddd, yyAug, Wednesday, 86
8/6/1986d-mmm6-Aug
8/6/1986dd/mm/yyyy06/08/1986

So today we learnt how to have a perfect date with dates!

If you like this tutorial then don’t forget to checkout the following tutorials as well:

  1. Using Excel Custom Number Formatting as Data Validation Tool
  2. Custom Data Labels with Colors and Symbols in Excel Charts – [How To]
  3. Dealing with Telephone numbers in Excel – Dialing in the correct format
  4. Formatting Numbers in Excel – Conditional Formatting Vs Custom Number Formatting
  5. Changing numbers Sign in Excel – From Negative to Positive and Vice versa

3 COMMENTS

  1. For those considering Conditional formatting – the current implementation within excel seems to be causing slow response and then as Excel motes and records the changes to CF structure – getting on to very slow and UNDO – well wait a week or so mode.

    So be sparing in your use of CF
    techniques such as have a single warning cell if any of a range would have got highlighted
    so just 1 CF and maybe a formula such as checking sumproduct rather than every cell of a range being separately checked and highlighted.

    From stackoverflow

    [condition 1]custom format 1;[condition 2]custom format 2;other number format;text format
    Where condition 1 defaults to >0 and condition 2 defaults to 20]£#,##0.00;[Red]-£#,##0.00;[Blue]”not supposed to be zero”
    more than 20 show as yellow
    between 0 and 20 show with a –
    negative show with a doubled – 1 from the format spec and the other because the number is negative
    and [Blue] with the message “not supposed to be zero”

    Nice to know about the mmmmm
    also #, shows thousands #,, does not show the thousands and %%
    And color names allowed brackets [black] [white] [red][green] [blue] [yellow] [magenta] [cyan]. .

  2. Hi Haasan
    Please assist
    I am utilizing an excel spreadsheet to generate invoices
    How do I go about automating the invoice numbers to change automatically

    • Briefly –
      Excel is not setup to do that sort of thing – you would be looking at a database facility to autosequence

      At first rownumber seems useful – but a major business need would be to ensure that no invoice number (reference) is used more than once
      So – firstly note number is something you do arithmetic with a reference may be a string of digits but not manipulated arithmetically.
      So you would need a separate store for the last number used in an invoice reference
      add 1 to that when starting an invoice entry and save the file that contains that number
      Then take the number and use it within the invoice reference string (format as “00000” or something similar to keep the invoice reference to a standard.

      But while the incrementation and use is simple, ensuring no reuse is fraught – cannot even use a datetimestamp – as that relies on the system time being set accurately, and never using summer time

      Then again – hopefully, the forum (pakaccountant) staff may have a good solution for the problem

LEAVE A REPLY

Please enter your comment!
Please enter your name here