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:
- Find the date of the first day of the month
- Find the date of last day of the month
- Find the date of the first day of the previous month
- Find the date of the second Wednesday of the month
The first thing we have to learn before we start manipulating dates are the YEAR, MONTH and DAY functions.
YEAR, MONTH & DAY Functions
- The YEAR function returns a four digit year (a number from 1900 to 9999) for a given date value.
- The MONTH function returns the month (a number from 1 to 12) for a given date value.
- The DAY function returns the day of the month (a number from 1 to 31) for a given date value.
So, if we have the date 14-Jun-2010 in cell B1 we would see:
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?
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…
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.
This task is a bit more complex, so we’ll take it step by step.
First we find the first day of the month:
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:
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.
