In this article I will write about date format in Excel, to be more precise: using the available and predefined formats but also how you can make a custom format of the date.
Remember that date format may differ, depending on the country you live in. For example, in USA the default date format is mm/dd/yyyy, meaning: 10/11/2012 or October 11, 2012. In Romania it is dd.mm.yyyy, meaning: 11.10.2012 or 11 October 2012. Your computer uses the settings for the date and time that you have given to it when you installed your operating system and made you choose the country. You can always check or change this setting in Windows by going to Start–>Settings–>Control Panel–>Regional and Language Options. In the window that appeared change the current format to whatever you want, then hit Apply and OK. It’s good to know that because when you type a date in an Excel cell that doesn’t match the format given in the Regional and Language Options, it automatically converts the date to the format you have selected.
In order to change the date format In Excel select the cell or cells you want to modify, then go to the Home tab, search for the Number Group and choose a format from the drop down list. If you can’t find what you want, click on the little button in the bottom-right corner, like in the image:
Another way to open the Format Cells window is to right click on the cell or cells you want to change and then hit Format Cells or if you want a faster way, press the key combination: CTRL+1.
You can either use an available date format or a custom one by selecting Custom from category and modify a predefined code if you don’t want to write it from zero.
The Codes for the date format in Excel
So let’s see the codes for the date format in Excel and what they display. I will take as example the date of March 6 2012 or 03/06/2012.
m – it displays the number of the month without the 0 which replaces the missing digit, in our case it will show: 3
mm – it displays the number of the month but with the 0 replacing the missing digit: 03; if the month was 11 then it would have displayed 11 because there’s no digit missing
mmm – it displays the short name of the month : Mar
mmmm – it displays the entire name of the month: March
d – it displays the day, in our case: 6
dd – it displays the day but with the 0 replacing the missing digit: 06; if the day was 13 then it would have displayed 13 because there’s no digit missing
ddd – it displays the short name of the day : Tue
dddd – it displays the entire name of the day: Tuesday
y y – it displays the last 2 digits of the year number: 12
yyyy – it displays the whole year number: 2012
For time, the code is made from h, m and s and of course, you can put the 0 instead of the missing digits by writing the code time like this: hh:mm:ss. You can also add AM/PM after the code.
Examples of date format in Excel
Given the codes presented, you can write the date in various ways:
Example 1: Mar-06-12 –> write the following code in the “type” box: mmm-dd-yy
Example 2: March-Tuesday 06-2012 06:56:04–>code: mmmm-dddd dd-yyyy hh:mm:ss
Example 3: Mar/06 Tue/12–>code: mmm/dd ddd/yy
Of course you can add other separating symbol or you can just let date or time together, like: Example 4: 030612/065604–>code mmddyy/hhmmss
I hope I managed to show you that date format in Excel is piece of cake and you can now display it the way you want.