There have been a few addional updates to the the list of shortcuts for Office 2007. As many are not familiar with some of the new shortcuts I thought I would post the list on the blog. Click on Read the rest of this entry below to view the list.  If you would like shortcuts for other programs please drop me a comment or an email.

I have found a video on Google Video’s that will take you through each step of creating a relative or absolute formula.

Keep Excelling

The complication arises due to the fact that months may have 28, 29,30 or 31 days.

I will take you through the thought process of getting to the final formula.

Step 1: Set up the spreadsheet as can be seen below:

Step 2: In cell B2 we will start off with a simple date formula. =DATE(2009,6,4)

This will return a date “4 June 2009″. (The format may vary based on the date format of the cell). This formula consists of 3 parameters. Year,Month and Day and returns a result that is a date.

Step 3: One can also identify a Year, Month or Day from a date via the following formulas:

Year: =YEAR(A2)        This will return 2009

Month: =MONTH(A2)   This will return 6

Day: =DAY(A2)       This will return 4

Step 4: Lets now substitute the Year and Month (not the day) into our formula

= DATE(YEAR(A2),MONTH(A2),4)

This will still return the date “4 June 2009″. The challenge now is to substitute the “Day” portion of the formula with the last day of the month.

Since we do not easily know what the last day of the month is we can use the following logic:

Select the first day of the next month and then subtract 1 day. We would do this by adding 1 month to the month portion of the date formula like this:

• = DATE(YEAR(A2),MONTH(A2)+1,4)   This will return 4 July 2009 in our example.

We can then hardcode the first day of the month as it will always be 1, like this:

• = DATE(YEAR(A2),MONTH(A2)+1,1)   This will return 1 July 2009 in our example.

The final step would be to subtract 1 from the final date and you will be left with the
last day of the previous month:

• = DATE(YEAR(A2),MONTH(A2)+1,1)-1   This will return 30 June 2009 in our example.

You can view the final formula in the spreadsheet image below.

Keep Excelling!!

He is enthusiastic about photography and has started a blog on the topic.

Please go and check it out on www.kidzphotography.co.za

This menu gives users access to the following fuctionality:

1. Format of the content of the cell e.g. text, number, date etc
2. Alignment of the content of the cell
3. Choice of font
4. Cell borders
5. Cell fill
6. Cell protection

Many of these features will be discussed in detail in future articles, however a very useful shortcut to get to this menu is the key combination of Ctrl+1

Version: 1.0

General Information

Executive Summary

This security update resolves a privately reported and a publicly disclosed vulnerability. The vulnerabilities could allow remote code execution if the user opens a specially crafted Excel file. An attacker who successfully exploited these vulnerabilities could take complete control of an affected system. An attacker could then install programs; view, change, or delete data; or create new accounts with full user rights. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.

This security update is rated Critical for all supported editions of Microsoft Office Excel 2000. For all supported editions of Microsoft Office Excel 2002, Microsoft Office Excel 2003, Microsoft Office Excel 2007, Microsoft Office 2004 for Mac, and Microsoft Office 2008 for Mac; all supported versions of Microsoft Office Excel Viewer; and Microsoft Office Compatibility Pack Service Pack 1, this security update is rated Important. For more information, see the subsection, Affected and Non-Affected Software, in this section.

This security update addresses these vulnerabilities by modifying the way that Microsoft Office Excel opens Excel files. For more information about the vulnerability, see the Frequently Asked Questions (FAQ) subsection for the specific vulnerability entry under the next section, Vulnerability Information.

This security update also addresses the vulnerability first described in Microsoft Security Advisory 968272.

Recommendation. Microsoft recommends that customers apply the update immediately.

Many people used to access it from the button on the top of the print preview screen (Excel 2003 and prior). It is longer there.

It can now be found in one of two places.

1. At the bottom of the Excel window (usually just below the horizontal scroll bar to the left of the zoom control) there are 3 buttons. The 3rd button to the right is the Page Break Preview Button. Press this button to enter into the Page Break Preview mode. To exit the mode press the 1st button (called normal). This will return you to the normal view. See the image below to help you locate the relevant buttons.

2. The second method of toggling between the Page Break Preview view and the Normal view is on the View Ribbon. 1st click on the View Ribbon. Then select the Page Break Preview button or the Normal button to move between the 2 views. See the image below to help you locate the relevant buttons.

We encourage people to share this blog with others as well as post comments and questions.

We will from time to time post challenges out to the general public as well as ask people to vote on issues.

We also intend to share the good, the bad and the ugly issues surrounding Excel. Microsoft has done superb work on Excel yet there are issues that need to be addressed, and we hope that discussions around these issues will encourage Microsoft to take note and address them.

We hope that people find this blog usefull.