Excel Date functions – Can’t understand them, Can’t work without them

Working with dates in Microsoft Excel can be a confusing task for anyone new to the system, but date calculations are so common in business and life it is bound to be something you must face at some point in your spreadsheet work. In this introduction we will look at how you can easily manipulate dates in Excel to give you the answers you need.

How Excel stores Dates

In Excel a date is stored as a number which represents the number of days since 0-Jan-1900. For example the date 30-Nov-2003 will be stored as 37955 because there are 37955 days between 30-Nov-2003 and 0-Jan-1900.

This means that Excel can do all kinds of clever things, such as compare dates, add or subtract, and also give you information about the dates such as which day of the week a date lands on, and so on.

Working with Excel Date Functions

To really explore the Microsoft Excel date functions, we will take a look at the following things:

The first thing we have to learn before we start manipulating dates are the YEAR, MONTH and DAY functions.

YEAR, MONTH & DAY Functions

So, if we have the date 14-Jun-2010 in cell B1 we would see:

Year(B1)=2010; Month(B1)=6; Day(B1)=14

OK, that’s cool, let’s start the DATE manipulations.

Working with Dates

So we have the date 14-Jun-2010 in cell B1. If we want to find the date of the first day of the month, all we have to do is create a date with the same Year and Month as B1 and change the day to 1. Simple, right?

=DATE(YEAR(B1),MONTH(B1),1)

What can we do if we want to find the first day of the previous month?

We already know how to find the first day (just put 1 in the DAY argument), so for the previous month we just subtract 1 from the MONTH. The YEAR will stay the same, and there you go…

=DATE(YEAR(B1),MONTH(B1)-1,1)

Note: This works even for dates in January because Excel’s Date function can handle 0 and negative numbers correctly

To find the last day of the month is a little bit trickier. The solution is not straight forward as before because each month has a different number of days. This is where understanding how DATE is stored makes the difference.

We know that DATE is actually a number, so why not subtract 1 from the first day of the next month?

We use the same Year as B1, Add 1 to the month, and change the day to 1, this will give us the first day of the following month (1-Jul-2010), now we subtract 1 (remember, the DATE is stored as a number) and we get the last day of the previous month.

=DATE(YEAR(B1),MONTH(B1)+1,1)-1

OK, now we are getting fancy! Let’s find the second Wednesday of the month …

This task is a bit more complex, so we’ll take it step by step.

First we find the first day of the month:

=DATE(YEAR(B1),MONTH(B1),1)

Now we will use another date manipulating function called WEEKDAY.

The WEEKDAY function returns a number representing the day of the week given a date value. In our case the function returns a value which is 1 (Sunday) through 7 (Saturday). It can return other value ranges, given a return_value parameter, but we will not go into that.

=WEEKDAY(B1) => 3  It's a Tuesday!

So now we know that the 1-Jun-2010 is a Tuesday.

We want to find the second Wednesday of the month which means we need to add 1 day to get to the first Wednesday and another 7 days to get to the second.

But what if the first day of the month was a Thursday? Then we would need to add 6 days + 7 days…

Excel’s CHOOSE function is perfect for us, It returns a value from a list of values based on the index_num parameter:

CHOOSE(index_num, value1, value2, value3 ... valuen)
index_num = 1 returns value1
index_num = n returns valuen

CHOOSE(WEEKDAY(B1),3,2,1,0,6,5,4) will give us the number of days until the first Wednesday, in our case WEEKDAY(B1) => 3 therefore CHOOSE will return 1.

So here is the answer:

=B3 + CHOOSE(WEEKDAY(B3),3,2,1,0,6,5,4)+7

Yahoo! It’s a Wednesday!

Summary

We have only really touched on the full functionality that Excel can offer when it comes to dates, but I hope this article goes some way to helping you get your head around these powerful functions.