On attached Excel file there are problems with the formulas setting day of the week. (=TEXT(WEEKDAY($M$2),“ddd”) for instance.
When converted to PDF user will get the week starting at Tuesday instead of Monday (on original)
Hi,
Thanks for your posting and using Aspose.Cells.
We were able to observe this issue as per your description with the following sample code and found that week is starting from Tue instead of Mon.
We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as
- CELLSNET-45332 - Week is starting from Tuesday instead of Monday unlike Excel
I have also attached the output pdf generated with this code as well as the screenshot highlighting the issue by comparison of the input and output files for a reference.
C#
Workbook wb = new Workbook(“Rw-26689.xlsx”);
wb.CalculateFormula();
PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;
wb.Save(“output.pdf”, opts);
Hi,
We did evaluate your issue (logged earlier as “CELLSNET-45332”). Well, it is MS Excel’s bug for formatting date prior to 3/1/1900. When formatting 2 to a weekday, in fact it is formatting 1/2/1900, this day is Tuesday but MS Excel formats it as Monday. I am afraid, we cannot format the date to a wrong value by default (such as DateTime.ToString()). We think you should change your formulas to avoid using such an odd value.
Thank you.
The reply is a bit confusing- these dates are not pulling from a pre-1900 date. Are you saying that this bug will effect any date? The formula seems rather simple- it just wants 3 characters of a day of the week- using the date value in M2.
How might I change the formula to return a value Aspose could deal with.
Also, this is problematic as MS to PDF and Acrobat Conversion get this right- what is it they are doing that Aspose is not?
Hi,
Thanks for your posting and using Aspose.Cells.
Just put 2 in some cell and then format it as Date, you will see, it will give you Monday, but it is actually Tuesday as we have shown in this screenshot. So, it is Microsoft Excel bug which should be fixed by Microsoft Excel Development Team.
For your formula, such as H4: =TEXT(WEEKDAY($M$2),“ddd”), the date used to format as “ddd” is not the date in M2, but the calculated result of WEEKDAY(M2), which is 2. So, the formula in fact is =TEXT(2,“ddd”). For value 2, it will be taken as 1/2/1900 and MS Excel gives the week of it as “Mon” but it should be “Tue”. For your situation, if you want to get the week day of M2, you can just use formula: =TEXT($M$2,“ddd”), =TEXT($M$2+1,“ddd”)…
And we found you talked about “MS to PDF and Acrobat Conversion” many times in your separate posts. We are not sure what are those two features or tools? And what is the right result and what is the wrong? Such as this one, “this is problematic as MS to PDF and Acrobat Conversion get this right”, how did you get and compare the result of “MS to PDF” and “Acrobat Conversion”?
I will see if using this other type of formula will be a good workaround for the customer- thanks.
When I am mentioning MS to PDF, I am talking about the office export to pdf function within office. When I mention Acrobat conversion I am talking about Adobe Acrobat’s Office conversion process (a tabin that gets put in office when you install Acrobat).
To give some context I am working for Bluebeam Software and we use your libraries to convert Excel and Word documents to PDF for use in our program Revu. Many of these issues are reported by our customers.
In regards to this issue and others- I am using the two comparisons (office and acrobat)- because we can expect our customers to compare our results to those two and if they can convert without issue it is a larger problem for us.
Hi,
Thanks for your clarifications and using Aspose.Cells.
We are afraid, we only support the rendering of Excel to Pdf as per Microsoft Excel Print Preview. If the exported Pdf matches with Microsoft Excel Print Preview (or exported Pdf by Microsoft Excel), we take it as correct and do not consider it as a bug.