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
Replies (3)
Please login or register to join the discussion.
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".
An alternative ...
... is to look at the EDATE() formula in the analysis toolpak.
With kind regards
Clint Westwood
or even EOMONTH()
If you are working in month ends then another Toolpak function - EOMONTH() would be useful. Similar to EDATE() but gives the month end