[svlug] OT: OOo Calc increment month - fix error

Marco Walther marco at sonic.net
Thu Apr 6 08:27:04 PDT 2006


> On 4/4/06, Marco Walther <marco at sonic.net> wrote:
>> On Tuesday 04 April 2006 20:21, Richard Mancusi wrote:
>> > I now know this is off topic, however I feel obligated to
>> > correct my post.  I said the following works perfectly:
>> >
>> > =DATE(YEAR(that cell);MONTH(that cell)+1;DAY(that cell))
>>
>> Does the following solve your problem?
>>
>> =DATE(YEAR(EDATE(A1;1));MONTH(EDATE(A1;1));DAY(EDATE(A1;1)))
>>
>> ??
>>
>> Thanks,
>> -- Marco
>
> No, but thanks for the idea.  It yields:
> 01/31/2006
> 02/28/2006
> 03/28/2006
> 04/28/2006
> etc
>
> It selected the correct Feb date, but was then "stuck" on 28.

The following should go to the end of the next month iff the date is at
the end of the current month. Otherwise it will advance to the same day at
the next month but not after the end of that month.

=IF(DAY(A1)=DAY(EOMONTH(A1;0));DATE(YEAR(EOMONTH(A1;1));MONTH(EOMONTH(A1;1));DAY(EOMONTH(A1;1)));DATE(YEAR(EDATE(A1;1));MONTH(EDATE(A1;1));DAY(EDATE(A1;1))))

I never though I could do something useful in spreadsheets;-)

Thanks,
-- Marco

>
> Rich
>
> _______________________________________________
> svlug mailing list
> svlug at lists.svlug.org
> http://lists.svlug.org/lists/listinfo/svlug
>
>






More information about the svlug mailing list