Calculate for EOMONTH, EODATE doesn't work properly

[jsfkcz]

Hello,
I have some formulas with EOMONTH and EDATE function in my "template":
The first one uses named cell and the second one uses an address.

After writting new date, only the simple formula (without eomonth,edate) has correct result, but after Ctr+Alt+F9 everything become OK.
Template

Result

(tested at 5.20.39 and 5.20.40)

The template and result are at http://www.genet.cz/kohout/xlsrw/xlsrw_ ... _edate.zip for download.

Josef Kohout

[jsfkcz]

Please check calculate for EOMONTH, EODATE functions - it still doesn't work under 5.20.42

Josef Kohout

[larsa]

Hello

The EDATE function is not supported. This will be fixed in the next update.

I can't find any problems with EOMONTH. As the calculation is very simple, please check that there not is a bug in the delphi RTL.
Here is the code to calculate EOMONTH:

Code: Select all

uses DateUtils; 
function EOMONTH(ADate: TDateTime; AMonths: integer): TDateTime;
begin
  Result := IncMonth(ADate,AMonths);
  Result := EndOfTheMonth(Result);
end;

[jsfkcz]

Hello,
I made a lot of next tests.

At last there is no problem in the XLSRW5, there is problem in differences in excel versions 2007/2013.

In our application, we use two ways for writting value to the cell by XLSRW and usually it works fine.
1. for strings we use Sheet.AsWideString[ColumnAsInt,RowAsInt]=@VarCharVariable
2. for other types we use Sheet.AsVariant[ColumnAsInt,RowAsInt]=@VariantVariable
We let Excel to convert variant value to another type by itself.

In My example I had @vResult as DATE
Excel 2007 interpreted my "date" value 2015-03-10 by other way then Excel 2013.
After open the result file in Excel 2007, Excel 2007 needed next hand calculate Ctrl+Alt+F9 to show correct values.
After open the result file in Excel 2013, Excel 2013 showed correct values immediatelly.

Example of possible solution for excel 2007:
@VarianVariable=cast(@vResult as smalldatetime)
@VarianVariable=cast(@vResult as datetime)

===================
@VarianVariable=cast(@vResult as date) --> err , needs Ctrl+Alt+F9 -- (I used declaration @vResult as DATE, it works correct only in excel 2013)
@VarianVariable=cast(@vResult as datetime2) -- permanent err

Hello

The EDATE function is not supported. This will be fixed in the next update.

I can't find any problems with EOMONTH. As the calculation is very simple, please check that there not is a bug in the delphi RTL.
Here is the code to calculate EOMONTH:

Code: Select all

uses DateUtils; 
function EOMONTH(ADate: TDateTime; AMonths: integer): TDateTime;
begin
  Result := IncMonth(ADate,AMonths);
  Result := EndOfTheMonth(Result);
end;

[jsfkcz]

Now, EOMONTH woks fine under XLSRW 2.00.05 .