Help with MS Excel formaula required
Can any one help me with this formula?
I am trying to input a due date i.e. if cell A1 has a date of 31 March 2009 I want to cell B1 to automatically input the 9 months deadline for Companies House.
Can anyone offer assistance?
Thank
Comments
An alternative ...
|
Thu, 04/03/2010 - 11:19 |
Permalink
... is to look at the EDATE() formula in the analysis toolpak.
With kind regards
Clint Westwood
or even EOMONTH()
|
Fri, 05/03/2010 - 08:35 |
Permalink
If you are working in month ends then another Toolpak function - EOMONTH() would be useful. Similar to EDATE() but gives the month end
Assuming that.....
The initial date is at a month end and that CH now accept the end of the month for filing (as opposed to e.g. 28 November) then the following would do it:
Assume year end date is in A1, =date(year(a1),month(a1)+10,0)
It would be better not to hard code the "10" but reference it to a cell which can be updated as necessary.To make the time limit more understandable, something like:
=date(year(a1),month(a1)+{cell reference to 9}+1,0)
The "0" for the day selects the last day of the previous month and hence the "10" instead of the expected "9".