 Formulas

How to use Date Format in Excel

Are you new to Excel? Wondering about what is a Date format in Excel? Does everything seems complicated? No worries at all, read the entire article in order to understand Date format in Excel. At the end you will definitely find it simple and useful.

Introduction to Date Format

The Date format in Excel works as per the Date settings in your system. For example, if your system has a default format of date as dd-mm-yy, then the dates will be shown in that particular format only.

• If you wish to change the format settings, then,

You can go to the Control Panel >> Region >> There Choose any suitable format >> click on OK.

For example, in the above image I have selected the format as dd-mm-yyyy.

• Excel does not store date as a date format. To Excel everything is a number, it stores dates in sequential serial numbers.

Let’s see how does this happen.

For example, we have a date as shown below.

Now, if I change the format from Date to General, then you will get a number as shown below.

You may think it is some random number, but this is not the case.

Let us understand why we get a serial number like this in a more detailed way.

• Suppose, you enter number 1 in any of the cells in Excel and change the formatting from General to Date, you will the result as shown in the image below.

You can see that we got a date as 01-01-1900. Now, you may ask why this date? This is because Excel starts its date from 1st January 1900, that is why the serial number for this is 1. Similarly, if I write 2 in any of the cells, it will give me a date as 2nd January 1900.

This means the serial number 2 represents 2nd Jan 1900, which is two day away from the 1st Jan 1900.

Now, you might have understood why we got a serial number as 44075. This because the date 1st September 2020 is 44075 days away from the date 1st Jan 1900.

So, this how dates work in Excel. Excel does this to make the calculations easy.

Now, let us understand what D, DD, DDD and DDDD represents.

Suppose, we have a date as 01-09-2020, to know what does D, DD, DDD and DDDD represent, we will use the Text function.

The SYNTAX for TEXT Function is:

=TEXT(value,format_text)

To get a clear idea look at the image below.

In the above image you can see that for value I have selected the date cell and for format_text I have given B4 as the cell reference. You might also see that I have put the \$ (dollar) sign before F and before 5, this will make the date permanent in the formula, when we copy the formula till the cell B7. So, that means only the format_text will be changed. Now, press Enter and see the results.

You can see that we got the number 1, similarly, if you just drag the cursor and copy the formula, you will get the results as shown below.

As you notice, when we have,

• Single D, we got the number 1.
• Two times D, we got 01.
• Three times D, we got Tue, that is the day of that date.
• Four times D, we got the full spelling of the day, Tuesday.

Similar is the case for months and years too.

Look at the examples below.

Here, in the similar way it shows the Month number and then the month name. For year, when there is YY it shows only the last two digits of the year and when there is YYYY, it will show all the four digits of the year.

Now, let us take a look at some more formatting options.

Basic Date Formats

Step 1: Be on the cell where you want to change the Date format.

Step 2: Go to Home Tab >> Numbers Group >> More Number Formats.

Step 3: You will get a Format cells dialog box, where click on Date and then choose the suitable format from the lists as shown in the image below.

Shortcut key for Format cells dialog box is Ctrl+1.

Step 4: Click on OK and you will see the difference.

If you want to format on the basis of your preferences, then you can use custom Date Format.

Custom Date Format

Step 1: Be on the cell where you want custom date format to be shown.

Step 2: Press Ctrl 1 for format cells dialog box >> Custom >> Type your preferred date format. Here, I have typed dddd-dd-mm-yyyy, as I want even the day to be shown

Step 3: Click on OK.

In the above image you can see that the Date has been changed as per our desire.

So, this is all about the basic Date formats.