Highbrowse

The straightforward way to add six months to a date in Excel is to use a formula of the form:

=DATE(YEAR(A29),MONTH(A2)+6,DAY(A2)) where your source date is in cell A2.

However, due to the varying length of months, if your source date happens to be, for example, 31/1/2011 and your formula adds 5 months, then the result of the calculation will be 1/7/2011. Perhaps not what you were expecting.

In order to get round this problem, Excel provides the EDATE function, which will correctly add months to a date and take into account varying month lengths. In our example, EDATE gives the correct result of 30/6/2011.

(If EDATE does not function in your version of Excel, check that you have the Analysis Toolpak installed).

Requirement: column A in Excel contains a data validation drop-down menu which contains numbers from 1 to as many Objectives that have been inserted in another field somewhere. The requirement in column B is to create a numbered sequence via a formula which will automatically calculate in the form 1.2, 1.2, 1.3, 2.1, 2.2 etc. no matter how many numbers there are. This can be done in the following way:

=IF(OFFSET($B10,0,-1,1,1),IF(OFFSET($B10,0,-1,1,1)=

OFFSET($B10,-1,-1,1,1),$A10&”.”&

RIGHT(OFFSET($B10,-1,0,1,1),1)+1,$A10&”.1″),””)

The formula assumes that the count begins in row 10 and that there are no more than 9 subsets for any principal number.