An undocumented Excel Function!

I know it could be boring for you to learn about Excel functions, which are available in plenty, and on which Excel Help offers good insight. So, I chose to write about an Excel function today that exists and can be used but on which Excel Help has nothing to say.

You would know that the default way to find difference between two dates is to just use the subtract formula and format the result cell as number. For instance, if Cell A1 contains startdate and A2 contains the enddate, you would enter the formula “=A2-A1” in the result cell, and format it as number to see the days.

Today we are going to learn about a date related function, which is an undocumented Excel function – and is actually a nice utility for computing the difference between two dates. Beauty of this function is that you can get the difference expressed in days, months, years or a combination of any of these. The function is called “DATEDIF” and can produce some unimaginable (positivelyJ ) results, as we would see in the examples that follow.

Formula Syntax:

=DATEDIF(startdate, enddate, interval)

Here, the startdate  and enddate would just be cell references (like A2, A3) which hold the respective dates for which you want to compute the difference. The last parameter, interval, can take one of the following forms (include the quotes):

• “d” to get the result in number of days
• “m” for number of months
• “y” for number of years
• “ym” for number of months (ignoring the year)
• “yd” number of days (ignoring the year)
• “md” number of days (ignoring the month and year)

How it works:

I believe in the saying “A picture speaks a thousand words”. Take a look at the below screenshot which I have constructed for you - it would make things very clear than any number of explanations.

 Deploying a combination formula:

You can obviously make use of this function and deploy a combination trick to get the exact number of years, months and days neatly in your result. See the following picture to understand this – am giving you two variations, the second one would look more neat because I use an additional IF function to take care of the Zero value situation.

That completes the trick for the day. Spread the word around to your friends, if you liked this and find it to be of some use.

My upcoming book on Excel contains many more such tricks and practical insights into getting Excel work for you. Please take a moment to visit my earlier post to understand details about my new book, and make use of the special offer available now to order it at a discount.

My new book - now available at a special price!

Let me admit - am pretty impressed with the number of hits on this relatively new blog, and am very thankful to all of you, readers. As a special measure of thanks, am glad to present you a hard-to-resist offer - an opportunity to order my upcoming book at a whopping discount of 34%.

Yes, you read that right - 34%, but it may not last for too many days. Amazon has opened up this special pricing just for a few days, and if you are interested to get my book, please place your order using the below links, at the earliest.

Here are some details about my new book "Excel for the CFO" - slated for release by 1st November, 2012.

Brief of the title:

Written specifically for finance managers, Excel for the CFO explains the best features of Excel that allow for the automation of regular processes and help reduce the processing time spent on analytics.

The book explores the entire gamut of finance-related functions and is focused on practical approaches to using Excel—including Pivot Tables, Goal Seek, Scenario Builder, and VBA—in problem solving to deliver quality results.

Using case studies across all types of organizations to demonstrate the application of Excel-based automation, the scenarios covered include the automation of financial analysis models, the creation of income statement and balance sheet templates, converting numbers to words for check printing and much more.

Any finance executive who manages the company’s business affairs and makes critical decisions by analyzing data would be directly benefitted by using the tips and techniques presented in this guide.

Pricing:

List Price            : $29.95
Pre-order Price   : $19.77
You Save            : $10.18 (34%)

Amazon Link:
http://www.amazon.com/Excel-CFO-Professionals-P-Hari/dp/1615470115

Incidentally, my other books are available with some good deals. Take a look at these links:
Excel for the CEO - Print edition
Excel for the CEO Ebook (Kindle edition)
Excel for the Small Business Owner (Ebook)

Is it possible to copy Page Setup?

Many of us would have faced this situation - where we take a lot of time to do some specific page setups for a worksheet, and later realise that we did not select the other sheets while doing the setup.

The result? - you would have to do the whole thing all over again, because Excel does not offer a regular menu option to copy the page setup features. We have all sorts of copy-paste, including format painters but when it comes to Page Setup, none of those come to the rescue.

Never mind, trust the experts to always come up with an easy solution. Here we have a simple solution for this complex problem - one of the most appreciated tips from my book "Excel for the CEO".

Problem Statement:

Apply the specific page setup settings for one worksheet to the eight other worksheets in the same workbook.

There is no built-in option to do this in Excel.

Solution steps:

1. What you need to do is very simple – first place your cursor in the sheet that has the page setup you want to use.
2. Now select the sheets where you want to apply the page setup using the mouse cursor while holding down the Ctrl key. 
3. If you are using Excel 2003, select File → Page Setup from the Main menu and just click on OK. 
4. In Excel 2007 & later, Page Setup is available from the Page Layout tab of the Ribbon. Alternate is to select File->Print and then click on the small Page Setup button available under this. 
5. That’s all – your page settings are automatically transferred to the eight new sheets.

Note: Some of the tips shown here are extracts from my book on "Excel for the CEO" - details available at www.mrexcel.com/ceo.shtml. You can also find this in the ebook edition "Excel for the Small Business Owner" available for online ordering at www.mrexcel.com/sbo.shtml