The Learning Hour* – Week 8: Using the DATES functionality in Excel

Posted by Ashish Agarwal

1. A date in Excel is also a number

Every date on an Excel sheet can be converted into its corresponding number.

For example, the number 1 represents the date January 1, 1990

Let us see how we can find that:

Let us say we have the number 1 on a cell in Excel.

Now, if you go to the Number formatting section of the HOME tab, and change the formatting of the cell to lets say “Long date”,

The cell will show the value “01 January 1990”. See below:

Cool isn’t it?

Wondering what is the number for today’s date? Go and check now!

2. You can perform mathematical operations on cells containing dates.

Now, just like you would do on numbers, the cells containing Dates can perform maths operations on them.

Let us say you want to know what the date 95 days from today will be.

Today is May 9^th 2018.

If you simply add 95 to this number, you can find out the date of that day. See below:

That is really great, isn’t it?

3. ​Extracting the DAY, MONTH and YEAR of a date

Given any date, it is also very easy to extract the DAY, MONTH and YEAR of that date separately.

Let us say, we have this date on a cell.

To extract the DAY of this date, we can use the function: DAY (see below)

It will return us the corresponding DAY of that particular date.

Again, to extract the MONTH of this date, we can use the function MONTH (see below)

It will return us the numerical equivalent of the Month of a particular date.

In other words, it will return 1 for January and 12 for December

And finally to extract the YEAR of this date, we can use the function YEAR (see below)

It will return us the year value of the corresponding date we have selected.

4. Creating Date from a given DAY, MONTH and YEAR

Now, sometimes you may need to form a date given any particular day, month and year values.

Let us see how we do it.

For this, we can use the DATE function. See below:

It will return us the corresponding Date for the selected Day, Month and Year in the different cells.

So as you can see, you can do a lot of things with the Date functionalities of Excel.

Any cool trick up your sleeve?

Share with us all.

*The Learning Hour is a weekly knowledge sharing initiative of SKILLFIN LEARNING. To subscribe to this initiative, kindly mail us at [email protected]. We will add you to the distribution list.