Managing Dates

Simply put, dates are an everyday part of our life (pun intended). Even so, I've come across examples where more trouble was used than was necessary to extract or manipulate dates. In this post, I'd like to give an example on how to effectively use dates to create headings (possibly for memos, report headings, etc.).

To begin, we need a date to work with. We'll start by simply typing in a date (such as "06/11/2004" or "June 11, 2004"); but since this post is about using dates dynamically, we'll get a little more creative as well. Instead, let's say that we want to create a spreadsheet of dates where we start with a particular date (e.g., 12/31/2004) and then we want each subsequent cell to be the end of the following month (following our example, 1/31/2005).

You could manually do this or get creative with concatenation (which we'll do later in this article), but the simplest way to do this is with Excel's EOMONTH() function. This function might not be available to you right away; if you can't find the function or it gives you an error, you'll have to go to Tools | Add-Ins... and then click on the Analysis ToolPak add in to make it available. Now, we can actually write the formula.

1. Start by typing "12/31/2004" (or one of it's many variants) into cell A1.
2. Now, in cell A2, type =EOMONTH(A1, 1). This tells Excel that we want the end of the month following the value found in cell A1 (1/31/2004). If you wanted quarters (instead of months), you would simply change the 1 to a 3 (i.e., =EOMONTH(A1, 3)).
   
3. Copy the formula from A2 down through A10.

Note how each date is appropriately 1 month after the previous, and the days are the end of such months. If you copied this down through cell A39, you would even notice that this works for Leap Years as well!

Now that we have that squared away, we should move on to create headers from all our dates. For example, let's say that we wanted to create text of the form "For the 3 months ended March, 2005", but change the "3", and "March, 2005" to whatever date we happen to be on. This is fairly straight forward once you understand concatenation (i.e., connecting various text items) and the TEXT() function. Concatenation can be accomplished with the "CONCATENATE" function, or by simply using an ampersand ("&"). We'll use the ampersand since it's faster to type.

1. To begin, go into cell B1 and begin by typing =TEXT(A1, "M"). This will give you the month extracted from the date (e.g., 12/31/2004 would give you 12) in a number format.
2. Move into cell C1 and type =TEXT(A1, "MMMM, YYYY"). This is similar to the last example, but gives you the "long" version of the month (e.g., December) followed by a comma and finally ended by the "long" form of the year (2004).
3. Now, move into cell D1 and type =IF(MONTH(A1)=1,"month", "months"). This allows us to appropriately use "month" instead of "months" for January. It works by testing whether or not the month in cell A1 equals 1. If it does, it uses "month"; otherwise, it uses "months".
4. Finally, move into cell E1 and type ="For the " & B1 & " " & D1 & " ended " & C1. This one is a little more convoluted, but not bad. It simply concatenates the words "For the " to the value found in cell B1 followed by concatenating a space (" "), and so on to the end.

That's it! Copy the formulas in cells B1:E1 down as far as we have dates and each title will be appropriately updated. Experimenting with the "TEXT" function as well as with the "MONTH, YEAR, DAY" functions will help you develop more skills to appropriately extract the information you need. You no longer have to manually update ANY heading that can be extracted from dates already present in your spreadsheets!