DateDif Revisited

A little over a year ago, I discussed the obscure DateDif function, and it was a readers’ favorite.

As I mentioned in my 2009 post, Microsoft has for reasons only they know, mysteriously chosen not to include documented information of this fascinating function in Excel. DateDif is a very useful (I think it is essential) tool for doing calculations with dates. Here is a refresher with some additional good information.

The syntax of the function is as follows:

=DateDif(First Date, Second Date, Time Interval)

Where the Time Interval is expressed as follows:

d   (Days) = Number of days between the dates
m  (Months) = Complete calendar months between the dates
y   (Years) = Complete calendar years between the dates

Note: The Second Date must be greater than the First Date, or you will get a Number Error.

An entertaining application of this function is to nest the NOW() function into it and calculate a person’s age as follows (Note: the “BirthDate” can refer to an easily changed cell value):

=DateDif(BirthDate, NOW(), “y”)

Special Note: If you put the Time Interval in the function directly, be sure to put “quotation marks” around it (e.g. “m”). If you put it into the formula via a cell reference, do not use the quotation marks (e.g. the cell should contain m, not “m”).

There are a great many ways to use the DateDif function, so give it a try some time. You may wonder how you ever lived without it!

Dynamic Named Ranges

Nearly two years ago, (see my September 15, 2008 post), I wrote about the wisdom of using Named Ranges in Excel. Using a Named Range in a formula does away with the need to make the range an absolute reference because it will always point to the correct range, regardless of where you copy the formula.

The only problem with a Named Range is that if you have a static cell reference, (e.g. $A$1:$A$20), when you add additional data, you will typically need to go to the Name Manager and Change the reference. What a drag!

Happily, there is an easy solution. Excel allows you to enter a Formula in the Named Range reference. Although there are several formulas that work for this purpose, the following one is one of the most versatile. Let’s suppose that you have you data in Column B, and you do not want to include the header in B1. By using this formula, and assuming you do not have any blank cells, (which is a bad database management practice anyway…), you will include your entire range on a Dynamic basis:

=INDIRECT(CONCATENATE("$B$2:$B$", COUNTA($B:$B)))

As a consequence, more data can be added to your database, and your Named Ranges will always remain Up-to-Date with no muss, no fuss. Wow, that Rocks!

Importing Problem? Change Text to Numbers!

Many of us have occasions where we are importing files that were not created in Excel. Usually this causes no problems, but once in a while when you import from other sources, your numeric values may appear to be numbers, but they act like text!

Happily, this is easily solved by converting the values into numbers. To resolve this problem, simply do the following:

1. Click a blank cell in the worksheet, and Type the Number 1 in the cell

2. Right-click, (“Right-click is Our Friend”), and Copy the cell with the number 1 in it

3. Select the range that contains the values you are converting to numbers

4. Right-click and choose Paste Special

5. On the Paste Special menu, click Multiply, and then click OK

So how do you know if you have been successful? Well, you can set up a simple function to see if the values now act as numbers, or you can check out the alignment of the numbers in the cells. If you are using a common format, the numbers will be aligned on the right and the text values will be aligned on the left.

Importing from a source other than Excel? Keep this little trick in mind, and Easily Convert your Values to Numbers! Rock On!

Rock and Roll with Your Ctrl Key!

This week discussion is on Three Cool Things you can do with your Ctrl Key:

#1 - You can make Noncontiguous Selections of cells. Now that’s just a fancy-shmancy way of saying you can select any cells you want, regardless of whether they are next to each other in a column or row. Simply hold the Ctrl key while you click any single cells or select any range of cells.

Bonus: You can also use the AutoCalculate tool at the bottom of you worksheet to analyze any combination of individual cells you have selected by holding down on the Ctrl key.

#2 – Using the Ctrl key enables Fast Navigation. By holding the Ctrl key and any Arrow Key, (up, down, left, or right), you can Instantly Transport to the last populated cell in that direction.

Bonus: Hold down the Shift key while you click any Ctrl-Arrow Key combination to Select All the cells between where you are and where you are transporting to (“Beam me up, Scotty…”)

#3 – You can also use the Ctrl key to Speed Up Data Entry. Let’s say that you want to put the same number, same string of text, or formula into a series of cells. You could do it the slow way by typing the number, string, or formula into the first cell and then copying and pasting it into the destination cells. But by select all of the cells you want to populate, typing the entry, and then pressing Ctrl Enter (be sure not to merely hit Enter), All of Your Selected Cells will be Identically Populated Instantly!

Now, I ask you, Does that Rock or What?

Funky Histogram Anti-Chart

Charts are tremendous tools that visually represent the often dry data that is contained in a table or database. Sometimes it can be a hassle to create one using Excel standard chart-building tools, especially if you want something basic (but still dynamic).

Here is a way to create a Funky Histogram-Style Chart by employing the little-used REPT function. This function repeats a text string the number of times you specify. For instance, the following formulas returns seven dollar signs: =REPT(“$”, 7)

One nice aspect of this simple technique is that it works in Any Excel Version. (Something that can’t be said about the Data Bars feature in versions 2007 and 2003.)

Let’s say that you have a table with Months in Column A, Sales in Column B, and we want our Histogram Chart (let’s call it an Anti-Chart, since we are not using Excel’s typical charting capabilities). We can then insert the following function in our Chart Column D: =REPT(“$”, B2/1,000).

The reason we are dividing the number in B2 by one thousand is to keep the scale of the resulting Anti-Chart to a reasonable width. Simply enter the formula into cell D2 and drag it down to complete your visual illustration of your data. Bamm! Anti-Chart!