Apache OpenOffice (AOO) Bugzilla – Issue 29115
When reading an .xls file 'hour' function returns always 0
Last modified: 2004-05-14 10:25:21 UTC
I have some xls files with cells containing values like '8:52', '11:35', etc. (since I imported the spreadsheet from a plain text file, these values are text, not time values) When using the 'hour' function in excel, it manages properly the problem of having a time-like text cell an returns 8, 11, etc. However, if I read the same file with OOo 1.1.1 (Windows or Linux version) I get always 0. Note that the hour function in OOo 1.1.1 works properly on time values like '08:52:00', but this problem prevents us from using some files created with Microsoft Excel. We're using the locale version for Spain (ES). Let me know if you need the sample file.
Please attach a sample xls file using the URL above: "Create a new attachment".
Created attachment 15243 [details] sample spreadsheet with Hour function failing
I've just attached a sample file. Note that, since my locale is ES, function is written 'HORA'. That's not a problem. If you open the spreadsheet with excel, the hour function works properly, even though the argument cells are text and not time-values
yes, this is a classic difference between Calc and Excel where Calc does not treat text as values. This is well documented in issue 5658. The workaround to ensure compatability would be to remove the leading apostrophe (') from each cell in column C or change the formula in B e.g. B4 to =HOUR(VALUE(C4)). 'VALOR' in Spanish. I will close this as a duplicate of issue 5658 *** This issue has been marked as a duplicate of 5658 ***
closed