I had a quick look at this and I am unable to recreate it. I am
setting it up with the Excel cell format in english as [h]:mm:ss and I
get the same result 30:00:00 in Calc. Using the format hh:mm , I get
the result 06:00 in both applications. Can you attach a sample
spreadsheet to the link above 'Create a new attachment' so that we can
clarify this. thanks.

Please Attach the documents which make this problem, so we can test
it/faster to confirm.  
(Without the documents, we cannot confirm the problem easily/need more
time)
Don't forget to cut other part of the documents, so the file size is
small, but we still able to see the problem. 

Hi,

this is not a bug !

As John found out, the result in both cases is correct. The three '#'
marks only shows that the cell is not wide enough to display the
result. Just doubleclick on the column header and the result will be
displayed ( so make the cell wider ).

So I close it as invalid.

Frank

closed invalid

Created attachment 10041 [details]
XLS-file created by MS-Office XP (Dutch)

I added a XLS-sheet, created by MS-Office XP (Dutch).
I typed "15:00" in cell A1 and B1
I typed "=A1+B1" in cell C1
I changed the format of C1 into "[uu]:mm", this was "u:mm" by default.
  'u' stands for 'uur', the dutch word for 'hour'
When I open this in OO (Default-language=Dutch, rest=Default),
I see "###" in cell C1, when I make it wider, I see "4457612040235244".
The current format is than: "[h]:mm", where category=Time and the
Format column doesn't show anything.
When I select format "[UU]:MM", the correct output "30:00" is shown.

Note the difference between [h] and [UU]

Hope you can help me out with this one.

Hi Daniel,

if you set the locale / language under tools options Language to dutch
the error occurs, if you use the default setting on an English OOo
this can't be seen.

Frank

set the target

Just tested the same when creating the XLS in XP-English,
the result remained the same, a long number, without the correct format.

I will follow up with this.

Yes, we have a problem here. I agree with Frank, this error is not
seen in the default language locale setting but changing this to
'Dutch (Netherlands)' in Tools->Options->Languages->Locale setting
produces this error. 

mark as started.

The problem is that the result field is using a user built custom
format of [u]:hh. This format is not part of the available excel
formats. This format is saved by excel as [h]:mm despite the fact that
it in the Dutch locale. OOo attempts to compute this format and fails
because it is not recognised as a valid Dutch format. This explains
why OOo will successfully format this in the English locale or any
locale which uses [h] as hours. 

The workaround would be to use the built-in Excel time formats. These
will be successfully transformed by OOo e.g. [u]:mm:ss. So, submitter
it is not correct that you have saved this result (C1) field with
[uu]:mm but rather it is saved with a [u]:mm. Saving it as [uu]:mm
would result in the value 06:00 and this would be correct in both
Excel and OOo. So, can you clarify if you intended the result to be
the total elapsed hours or the elapsed hours expressed in 24:00 timescale?

If its the latter, then this issue is invalid. If its the former then
I need to ascertain if we intend to fix this anomaly. 

What I wish to see is total amount of elapsed hours in 24-hour format,
so the result I need in my spreadsheet is "30:00".
I've created the same sheet on a XP-English version, the same problem
occured. So it has probably nothing to do with the language of Office-XP.
I just tried it (create in XP, read in OO) with [u]:mm and [uu]:mm,
they both provide "30:00" as a result, not "6:00".
When I added the seconds in XP ("[u]:mm:ss"), the format is treaded
well by OO, but that was not the format I needed...

I meant uu:mm will give you the result of 06:00. I agree both your
custom made formats [u]:mm and [uu]:mm will provide the total elapsed
hours result of 30:00. So yes, there is definetly an interoperability
issue here. 
You also state that you can create this in from a document created in
Excel XP-English version in OOo. I cannot re-create this using an
English locale setting of OOo. What locale is OOo set to in
Tools->Options->Language Settings->Languages->Locale Setting? 

Language of Locale Settings = Default
Language of Default Currency = Default
Default Language for Documents Western = Dutch (Netherlands)
I just tested both (Dutch and English) files on OO in both settings
(Dutch and English), both files do not come up with "30:00".

When I open the files in OO (RC4 I think) on Linux, they come up with
"30:00", regardless if the Language is Dutch or English.

Setting your 'Language of locale = Default' means that OOo sets the
locale to the setting of your OS i.e. in your case the Windows XP
Regional Options->Locale. From your original document, this is set to
Dutch. Thus if you explicity set the OOo locale setting to an English
locale, you will see that that OOo works correctly.   

Okay, this works for me,
I need to use the English (UK) setting, otherwise I'll lose my default
24-hour format.
But testing a bit further now:

Create a XLS in OO:
A1="15:00" (represented as "15:00:00")
B1="15:00" (represented as "15:00:00")
C1="=A1+B1" (represented as "30:00:00")

Open the XLS in Office-XP:
A1 represents "15:00:00"
B1 represents "15:00:00"
C1 represents "1.25", not "30:00:00" as it did in OO.

Both OO and Office-XP have the Number-General format,
but OO represents it in time, and XP in an actual number.
I prefer (ofcourse) the time-format, but there's a difference.
When setting the desired format "[HH]:MM", they both give the correct
representation of "30:00".

In short: my issue has been solved (with in my opinion a workaround),
but there maybe (or perhaps not, cause it's an undefined format)
another issue.

Thanks a lot, I'll gonna use OO for these type of sheets from now on.

This is a bug in the number formatter's conversion mode between
different locales if time code keywords are localized and one of [h]
[m] [s] time code keywords is used. Only conversion to/from Dutch,
Finnish, Swedish, Danish, and Norwegian is affected.

Grabbing issue.

@OOarjan: Calc automatically displays the formula result in a suitable
time format if calculation involves values of cells with a time format
assigned, of course only if the formula cell doesn't have any other
number format assigned. Excel is a bit dumb there and displays the raw
serialized date value (read the value 1.25 as 1.25 days == 1.25*24
hours == 30 hours). If you want Excel to display the result formatted
as time, you'll have to explicitly assign a number format to the
formula cell before export.

Accepted.

On branch cws_src680_numforensis:
svtools/source/numbers/zforscan.cxx 1.36.124.1

Reopen to reassign.

Reassign to QA.

Restore status.

tk: verified on branch cws_src680_numforensis

closed because fix available in OOo1.9m65

Created attachment 73975