DATE function
On Sun, 4 May 2008 23:14:00 -0700, Learn-more
wrote:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
When using this to get the next month, find some problem when at end of a
long month (with 31 days), the result came back is the first day of 2 months
later. eg a1=31/3/2008 the result will be 1/5/2008 and not the expected
30/4/2008.
Any way to make it consistant to display end of month
Thanks
Rick gave you the answer if you have the Analysis ToolPak installed (or with
Excel 2007).
If you don't have that installed, you can use this formula:
=MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1}))
Note that the {2,0} and {0,1} factors are both array-constants (enclosed in
braces). But the formula is entered normally.
--ron
|