Secret DateDif Function in Excel Calculates Years, Months, Days

Sometimes, you need to show each employee’s age in Years and Months. The cool|secret|sometimes buggy =DATEDIF function does the trick.

This function has not been documented since Excel 2000, so figuring out the third argument is the trick. Enter “Y” to get full years. But, if you enter “M”, you are going to get the total number of months. Someone who is 49 years old will be reported as 588 months old, which is usually not what you are looking for. Instead, use “YM”, which I remember as “Months in excess of the full years reported by Y”.

For Days, you have “D” for total days (18075), or “YD” for days in excess of full years, or “MD” for days in excess of full months.

Why is this undocumented and sometimes buggy? If you have someone born on January 31 and you have an ending date of March 1, the YM reports 1 month and the MD reports Negative One days. But really, who actually employs one-month old babies these days? There are other strange pairs of dates which failed to work in Excel 2007 SP2, although those appear fixed in Excel 2010.

PS – Wow – it looks like I am 3 days away from my 49.5 half birthday! I better alert my family…

Wish Excel Had AutoAverage Like AutoSum?

It already does! Open the tiny dropdown arrow next to the AutoSum icon, and you have AutoAverage, AutoMax, AutoMin, and AutoCount!

This is one of the tips in Learn Excel 2007-2010 from MrExcel – 512 Excel Mysteries Solved.

Copy the Quick Stats Values to the Clipboard

The question came during an Excel seminar in Tampa: Wouldn’t it be cool if you could copy the statistics from the status bar to the clipboard for later pasting to a range?

Why One Comma in \$1234,567?

A week ago, I was invited by Areef Ali to do three Excel seminars in the beautiful twin-island republic of Trinidad and Tobago. At one of the breaks in the first day, one of the students in the class showed me the most perplexing problem in her Excel. She and her co-worker, both using two different laptops, typed in 1234567.89 and then pressed the Comma Style icon in the Home tab of the ribbon.

Her co-worker saw \$1,234,567.89, but on this computer, the result was formatted as \$1234,567.89!

I started looking through the Excel Options settings, and even got my PC and started comparing her Region and Language Settings in the Control Panel line-for-line. Everything apparently matched.

In the end, it turns out that a setting was “hiding” way up at the top of the Region and Language dialog… one PC had English – Trinidad and Tobago, and that was the one missing the comma. I asked the students in this general vicinity if their company ever used the \$1234,567 format and no one had ever heard of this.

Change this to English – US

So, to recap, if you find yourself with Excel missing commas beyond the first comma that separates thousands and hundreds, follow these steps:

1. Click the Start button in Windows 7
2. Type Region in the search box
3. Choose Region and Language in the search results
4. At the top of the Region and Language dialog, change the Format to English (United States)
5. Click OK

After the three days of seminars, we took the weekend to sight-see in Trinidad. It is a beautiful country. If you will excuse the non-Excel content, here are a few of our photos.

The highlight was the 1.2 km hike up a mountain to the Rio Seco waterfall (shown in the video). I know people say this, but I’ve never seen water this blue. When someone convinced me to try self-meditation 20 years ago, this is the place I would imagine me going. Of course, the hike was muddy, slippery, and at my age, I am lucky to have returned without a twisted ankle.

At the Rio Seco Waterfall, the water was so blue, but you could see the fish dart about.

A close second was watching the turtles on Grand Riviere beach on the north side of the island. From April through June, these massive turtles come up to lay eggs. 30 days later, tiny turtle hatchlings are born. After letting the hatchling imprint on the sand, the tourists now get the hatchlings to the water, avoiding the bird predators. But, then the hatchlings face a bevy of predators in the water. 15 years later, the lucky ones who survive come back to Grand Riviere to nest.

Leatherback Turtle at Grand Riviere. This momma turtle was “small” – her shell was only 6 feet from neck to tail.

Fishing boats in Grand Riviere

Mary Ellen Jelen holds a turtle hatchling.

Leatherback turtle uses back flippers to dig a nest

MrExcel uses his laptop as a Steel Pan

Learn Excel – “Eliminate Worksheet Bottlenecks with Fast Excel V3”: Podcast #1892

Microsoft Excel MVP Charles “Fast” Williams just released V3 of his Fast Excel add-in. I had the opportunity to see Charles demo this product recently in Amsterdam and it blew me away. Today, Learn Excel from MrExcel Podcast #1892 is a review of the and shows some of my favorite features of the product.

Bill’s Notes on This Product:

Profiler: Locate and eliminate Spreadsheet bottlenecks

Speed Tools:

• New Worksheet Functions for Lookup, Filter, and Array handling.
• Quickly reverse an Array.
• Count Unique Cell Values in a Range.
• Count Unique Rows in a Range.
• Return a list of Unique Items with the count of each item.
• Concatenate a Range of Cells without specifying each individual Cell.
• Super-fast Lookup Functions that do not require your Array to be Sorted.
• Advanced Llookup that will return 2nd match, last match, first match, or all matches.

Manager: Name Manager, Formula Manager Tools

There are al-a-carte pieces you can buy for \$89, \$59, \$59, \$29 and\$29 depending on your needs. But, until the end of July 2014, you can get the Whole  Fast Excel add-in Suite for Half Price at \$94.50. Judging by what I’ve seen and was able to do -easily- with the Fast Excel add-in, this seems to be the way to go.