Issue 29115 - When reading an .xls file 'hour' function returns always 0
Summary: When reading an .xls file 'hour' function returns always 0
Status: CLOSED DUPLICATE of issue 5658
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 1.1.1
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
Depends on:
Reported: 2004-05-13 07:13 UTC by dmrq
Modified: 2004-05-14 10:25 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

sample spreadsheet with Hour function failing (106.50 KB, application/
2004-05-13 18:43 UTC, dmrq
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description dmrq 2004-05-13 07:13:14 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.
Comment 1 john.marmion 2004-05-13 09:02:42 UTC
Please attach a sample xls file using the URL above: "Create a new attachment". 
Comment 2 dmrq 2004-05-13 18:43:23 UTC
Created attachment 15243 [details]
sample spreadsheet with Hour function failing
Comment 3 dmrq 2004-05-13 18:45:22 UTC
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
Comment 4 john.marmion 2004-05-14 10:24:13 UTC
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 ***
Comment 5 john.marmion 2004-05-14 10:25:21 UTC