|
I don't know about you, but I used to have trouble getting dates. Someone would say to me, "Let's meet again in six weeks," and I'd think, Can I do that? What day is that? Or someone would tell me that I had a 30-day grace period after my license plates expired before the police would start coming after me, and I'd think, What date are the police going to start coming after me? I could always figure it out using a "calendar," of course, but I don't like figuring things out the hard way. I do enough of that in life's other many arenas.
I have come to rely, therefore, on a handful of simple date calculations in a single Excel worksheet that I like to call, for good or for ill, Getting Dates.xls. It doesn't do a lot and it doesn't do anything very complicated, but it meets a real and practical need, and one that recurs often enough in my life to merit an Itch Scratcher Award every time I use it. It's a keeper. Best of all, my wife doesn't object.
There's more to it than getting dates, though. I also use it to calculate the number of days between dates of various standard kinds. For example, sometimes I want to know how many days there are between now and some date in the future, such as a Computor Companion deadline; I have a calculation for that. I also have formulas for counting the number of days left in the year, the number of workdays left in the year, and the number of weekends left in the year. It has a few other very important ones, too, as you'll soon see.
The only trick to this, if there is a trick at all, lies in the fact that you have to load the Excel "Analysis ToolPak" to get some of the calculations to work. Yes, that is how it's really spelled.
As an aside, if this article were not about software, you would no doubt think I just misspelled the stem "pack" in the synthetic term "ToolPak." But since this is about software, I bet you didn't give it a second thought. You probably assumed—correctly—that I in fact spelled it just the way Microsoft wants me to spell it, even though there is no basis in reason to remove the c from "pack," let along capitalize the P. All we have done is make things harder for future archeologists who, while studying fragile fragments of stone with the term "Analysis ToolPak" chiseled on them, will have to spend time pondering whether this is an early or late derivative of the original term "pack," and whether it has subtle or important differences in meaning or is merely a misspelling. They will, of course, be wasting their time. Thus, I would like to know what benefit derives to civilized society from the spelling of "ToolPak" without a c. Anyone?
Okay, back to our spreadsheet. Adding the Analysis ToolPak add-in gives Excel access to one of the formulas you need to use: NETWORKDAYS(). Loading the Analysis ToolPak in Excel is simple:
1. Select the Tools, Add-Ins… command, revealing the helpful Add-Ins dialog box.
2. Under Add-Ins available, select Analysis ToolPak.
3. Click OK.
Once you've done that, you're ready to get all kinds of dates and date-related numbers. In each of the examples that follow, I present the same cells twice, the first time showing the formulas, and the second time showing the results.
Number of days between two dates
To get this number, simply subtract the one date from the other, as in the following example:
Before you object to the format of the dates in the first example, let me tell you that you don't have to enter them that way. Those are serial dates, tried and true, but you can enter them the old fashioned way, as they appear in the second example. You control their appearance the same way you control all formatting in Excel, with cell formatting commands. You should also know that if the result in column C doesn't appear as a number, you merely need to format the cell as a number (with no floating point), and it will appear correctly.
Number of work days between two dates
Here's where you need the NETWORKDAYS() formula:
Because Excel has an excellent help file and can explain the syntax of the formulas much better than I can, I won't go into the syntax of the formula here. But I will explain the "Holidays2007." That is the name of a range that contains all of the holidays in this year's calendar, a simple list that I created and added to the worksheet. For example, the office is closed on January 15th, so it doesn't count as a work day. The NETWORKDAYS() formula takes this into account.
Date that is x days after today
What day is 120 days after today? This formula will tell you. Even better, you can use whatever value for x you want, and you'll get the resulting date:
Notice the formula in cell A15, which refers to cell B2. In my Getting Dates.xls workbook, I've put a NOW() formula in cell B2 so that it always displays today's date. I do this because I need today's date in several other places in the worksheet, and this makes it easy to control that value in one place. I've also made it an absolute reference so it doesn't change whenever I copy it to another cell. But none of that is necessary; if you are so inclined, you could also dispense with cell A15 altogether and just put =NOW()+B15 in cell C15. You'd get the same result.
Notice also that I'm displaying the full date in cell C15. As in the first example above, you control that format with cell formatting, not the formula. I've chosen to display the full date because sometimes I want to know the day of the week as well as the date.
Date that is x days after date y
You'll see nothing new here, just an expansion of the variables allowed as inputs. Rather than assuming that the start date is today, the formula lets you specify it.
Countdown to vacation
Want to know how many days till vacation? Check it out:
You could even have some fun with conditional formatting as the countdown gets close.
With a little imagination, you can extend this basic concept to include other date-related calculations. For example, it wouldn't be too hard for Excel to count the number of days left in the year, the number of work days left in the year, the number of days till your spouse's birthday, or the number of days till your wedding anniversary. If it's important or if it'll happen more than a few times in your lifetime, you should consider adding it to your Getting Dates worksheet.
There are some things Excel cannot help you with, such as how to make the dietary requirements of high cholesterol interesting, but getting dates is definitely an Excel strong suit. If you want to know when something, Excel can probably ease the pain of finding it out.
And remember, you can always force Excel to format the dates the way you prefer to see them by specifying the look through cell formatting. Select the cells you want to format, select the Format, Cells… command, select the Number tab, select the Date category, and select the format you want to have displayed.
|
