Excel Date Format: Get Your Way with the Date!

Dates are obviously a very important part of data analysis, and our lives. We all know that feeling when a date’s going well, or that moment 2 minutes into a blind date when you realize it’s not working and wish you could vanish. Same for dates on your spreadsheet – that Oh No! moment when you’re presenting the sales figures to the board, and the data looks all wonky because you entered data in DD-MM-YY format, that the chart interpreted as MM-YY-DD. Yes, that’s happened, er, to me.

So in this article, I’m going to show you just how to manipulate your date, and make the most of it – that is, for the Excel date format. I assume you have a basic understanding of Excel. If not, you may want to first look up our Excel course for beginners . Or you could try out this course which makes learning Excel really easy.

The Default Date Format

When you enter a value like 10/12 in a cell, MS Excel assumes that you want to enter a date and it formats this value based on the default date setting in the Control Panel. For example, Excel would display the above value as 10-Dec if this were your default date format. However, if you want to try out a different format in your Excel worksheet, that is also possible . We’ll walk you through a few easy ways to do it.

Changing the Date Format using Right Click

1. Select the cell or range of cells containing the date(s) that you wish to change.

2. Right-click on the highlighted cell(s) and select the “Format Cells” option from the drop down menu.

3. Click on the “Number” tab located on the top-left corner of the dialog box.

4. You will find “Category” column on the left side. Here select the “Date” option.

5. Here you can select the preferred date from the list of options in the “Type” column on the right side.

6. Click “Ok” button to confirm the change.

To change date from the International to American format, in Locale (location) choose English(United States).

Format Dates in Descending Order

In Excel, you can not only change the date formats but if you want, you can also arrange the list either in ascending or descending format.  Here how to sort the dates in descending order.

1. Select the column which contains the date that you want to sort in the descending order.

2. Right click the selected date column and choose “Format Cells”.

3. Next select “Date” from the “Category” column and choose your preferred date format. This is to ensure that all dates in this column are in the same format, and are interpreted the same way.

4. Click the “Data” tab near the top of the window and select the “Sort” icon.

5. Click “Order” heading and select “Oldest to Newest.”

6. Click “Ok” button to sort your dates in descending order.

If you want to sort the date in the ascending order, follow all the steps up to step 4. Then in select Newest to Oldest in the “Sort” command and click Ok. Your dates are sorted in the ascending order.

A point to note: MS Excel can only sort data in the columns. It is not programmed to sort data in the horizontal rows. Yes, strange but true. If you must sort dates in a row, you’ll first have to convert the rows into columns.

Steps to Convert Date into String

There are times when you may want to convert the date into string to manipulate the data, say to find which one is later. There are functions where you can convert the date into string. Let’s take a look at how this is done.

1. Open an Excel worksheet which contains the date that needs to be converted into string.

2. Select a cell where you want to place the string. Here enter the function “=Text(” (without quotes).

3. Click on the cell which has the date format, the one which has to be converted into a string. For instance t(A1)

4. Now enter +0, “MM/DD/YYYY”) after the cell reference. It will look like this “=Text (A1+0,”MM/DD/YYYY”).” You can change the date format to your preferred format. Press the enter key and the end result would be the string version of the date.

How to Calculate a Quarter in Excel

A year consists of 12 months and quarter of a year has three months. Very often sales and marketing data is organized by quarters, and not months. Here’s how you can find out which quarter any date belongs to, or better still, have the quarter automatically populated in a cell, when you just enter the date.

1. If you chose the date format “14-Mar-2001” from the menu Format Cells, when you enter the value in the cell (A2) of your worksheet, “6/14/14”, it will automatically change into “14-Jun-2014”.

2. Now click on any cell where you want to display the quarter of the year for the date mentioned above “14-Jun-2014.” For instance C2

3. Enter the function “=Roundup (Month (A2)/3, 0)” in the cell without the quotes.  Here A2 is the cell reference. Press enter.

4. The answer will be “2” meaning the second quarter for the date 14-Jun-2014.

5. Whenever, you change in the cell value in A2. The quarter number in the corresponding cell will be updated automatically.

How to Convert Numbers to a Date in Excel

MS Excel is a powerful tool which has many options including one to convert numbers to date. Let’s see how to go about it.

1. Highlight the cells that you want to convert into date format.

2. Click “Data’ tab

3. Here you have select the “Text to Column”

4. Click “Next” button twice. You have to bypass the options such as changing the delimited or fixed width values.

5. Select the “Date” radio button.

6. Choose the date format of you choice from the drop-down menu.

7. Click the “Finish” button.

As  you can see by now, Excel has many ways to shows dates. Here’s an inside peek – Excel actually stores the dates as sequential numbers called serial values. The serial number or the date system for MS Excel for Windows begins from January 1,900 and for Macintosh it is January 2, 1904. This date system was introduced to enable better compatibility with other spreadsheet programs.

I hope this article helps you better understand how to format and manipulate dates in Excel. If you’d like a refresher at any point of time, do feel free to look up our course introducing Excel. Or if you’re already familiar with it you can learn some Excel best practices in this course.