June 12, 2004

The DATE function, Part 2

It's a funny quirk of business. "Payment at 30 days" doesn't necessarily mean payment at 30 days, it really means exactly one month after. For example, if I add 30 days to today's date (06/12/04), the answer is 07/12/04 only because June has 30 days. Try adding 60 days and the result will be 08/11/04. What should really be calculated is today plus 2 months which is 08/12/04.

With this in mind, there is a very useful function called the EDATE function. It works like this, =EDATE(Date,Months) so if I want to add 2 months to today I can enter =EDATE(TODAY(),2). (Don't forget that =TODAY() gives today's date)

The problem is that the Analysis Toolpak must be installed for EDATE function to work. To check whether it is installed, select Tools, Add-Ins and the dialog box will appear like this.

And if you don't have the Analysis Toolpak? Here is a workaround that I call EDATEPLUS. It uses the DATE function to add or subtract the number of months you want, and if the days of the calculated date exceed the number of days that actually exist in that month, it will calculate to the end of the month instead, this being selected by the IF function. (For example, if today was 01/31/04, then one month later will be 02/29/04, not 03/01/04).

Hold on to your seats...


A1 is the original date and B1 is the number of months to add or subtract. Place the formula in any cell other than A1 or B1.

Basically it works like this. If the day of the calculated date (from the original date plus or minus the required months) does not equal the day of the original date, calculate to the end of the month instead. (Actually the end of month can be calculated by using the EOMONTH fuction also available with the Analysis Toolpak, or as =DATE(YEAR(A1),MONTH(A1)+B1+1,0) without the Analysis Toolpak)

Here's a Workbook example to have a look at. If you don't have the Analysis Toolpak installed, the EDATE and EOMONTH formulas will not work and you will get a #NAME? error.

There is also an EDATEPLUS Addin with installation instructions included on the right sidebar. Beware that it will only work on the computer(s) it is installed in. The workarounds above and in the Workbook example should work in any computer 100% of the time.

June 10, 2004

The DATE function, Part 1

The DATE function is definitely one of my favorites. It works like this, =DATE(Year,Month,Day). So =DATE(2004,6,10) will give me June 10, 2004.

Here's a couple of useful tricks.

First of the Month
=TODAY will give today's date, so =DATE(YEAR(TODAY()),MONTH(TODAY()),1) will give you the first of the current month. Add or subtract to MONTH(TODAY()) for different months, as in =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1) will give you the first day of next month while =DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) will give you the first of last month.

End of the Month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) will give you the last day of the current month. Add or subtract months as above to go forward or backward in time as you like. Who said time travel was impossible?

A slightly more complex formula next time...

June 07, 2004

Hiding Stuff, Part 3

Hiding Sheets

To hide sheets, you can go to Format, Sheet and then select Hide or Unhide to hide or unhide any sheets you choose.

What's the advantage of hiding sheets? Well, if you are not using all of them, you might want to save some space where the Sheet Tabs are shown (too many tabs means not all will be visible and this will make harder to navigate around your Workbook)

You may also use a sheet for miscellaneous data such as pricing tables for LOOKUP or other reference formulas that are only referred to or updated recently. You might hide this sheet as a form of protection (you could protect the sheet and hide it to make it even safer. It might seem paranoid but just how important is your data?)

Or if you are really concerned about people snooping around you might consider making a sheet very hidden.

Making Sheets Very Hidden
By making your sheets very hidden, you are merely preventing it from being noticed from casual users. It won't appear under sheets to unhide using the Format, Sheets method. But I should say that anyone who knows about VBA (even low life sludge like myself) knows how to find and unhide Very Hidden sheets. That said, it should still be a reasonable measure of protection for around the office and such, but think very hard before you email a Workbook with sensitive information to people you don't want to see it in the first place!

Okay, assuming you still want to know, let's go to work. Open the Visual Basic Editor. There are a few ways to do this. One quick way is to use the shortcut keys Alt + F11. Another way is to right click any Sheet Tab and select View Code.

In the Project Explorer (top left) you will see a list of files. Let's say your Workbook is Book 1, navigate to the sheet you want. In this case I have chosen Sheet 3.

In the Properties Window (bottom left) select Visible. (It should appear in both the Alphabetic and Categorized tabs)

Click on the right and a drop down list should appear. Select Very Hidden and you're done.

So now you know. I just hope I haven't broken some little known taboo in the Excel community. If I don't post within the next few days, do me a favor and call Missing Persons!

This page is powered by Blogger. Isn't yours?