Excel 2007 vs. 2003 differencies for custom format [h]:mm:ss

[Dmitriy Shapiro]

Hi,

In one column (Excel 2003 Xml Spreadsheet document) we have cells with
duration data. Some cells are formatted as "h:mm:ss" (when duration is less
or equal to 24 hours) and some as "[h]:mm:ss" (when duration is longer then
24 hours). We did it in order to display durations correctly when the
document is open in Excel 2007. Otherwise, Excel 2007 adds 240 hours to
duration if we use only "[h]:mm:ss" format. The trick with mixing formats
works for displaying these durations. But it does not work for sorting and
charting, since Excel 2007 still adds 240 hours to every duration formatted
"h:mm:ss".

Please help.

Thanks.

 

[OssieMac]

Hi,

xl2007 doesn't add 240 hrs to the time for me. Perhaps you can post a little
more info like samples of data and how you are manipulating it.

Are you working entirely in times in cells formatted as time or are you
manipulating standard numerical entries and converting them to time. Seems to
me it is possibly the latter and you have some error of logic in how you are
manipulating them.

Just as a test to see how the times work:-
Open a new workbook
Format column A to [hh]:mm:ss
Enter 1:00:00 in the first cell
Autofill the cell down to 240 hours (or more)
Format the adjacent column B to number with about 4 decimal places.
In the first cell enter = A1
Copy the formula to the bottom of the first column.
Observe that when you get to 24hrs it becomes 1.0000
48 hrs becomes 2.0000 and so on.
Reason for this is that times are a fraction of one day and then when you
reach 24hrs it becomes 1 + the fraction then at 48 hrs 2 + the fraction.
However, when formatted with [hh]:mm:ss it keeps the actual hours instead of
placing the day in front.

As a further test format column C to dd mmm yyyy hh:mm:sss
Enter = A1 in the first cell
Autofill down
Observe that the times for the first day show as 00 Jan 1900 and do not
become
01 Jan 1900 until the end of the first day because only part of Jan 1 has
passed until midnight.

Hope this little explanation helps.

 

[Dmitriy Shapiro]

Hi,

Thanks for reply.

Here is a fragment of my Xml Speadsheet:

<Styles>
...
<Style ss:ID="s21">
<NumberFormat ss:Format="h:mm:ss"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
...
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell ss:StyleID="s21"><Data
ss:Type="DateTime">1899-12-31T01:09:45.000</Data></Cell>
</Row>
</Table>
...
</Worksheet>

When I open it in Excel 2007 I see "1:09:45"

When I try to plot the value it gets converted into "241:09:45"

Excel 2003 handles it with no problem.

 

[OssieMac]

Hi again,

I don't think that I can really help you. Am I correct in assuming that
1899-12-31 is meant to represent Zero time? If so you could try 00:00:00?

Don't have any other suggestions.