Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 vs. 2003 differencies for custom format [h]:mm:ss
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 vs. 2003 differencies for custom format [h]:mm:ss
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. -- Regards, OssieMac "Dmitriy Shapiro" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 vs. 2003 differencies for custom format [h]:mm:ss
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 vs. 2003 differencies for custom format [h]:mm:ss
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. -- Regards, OssieMac "Dmitriy Shapiro" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel opens two windows (2007 & 2003 format): I want only 2003. | Excel Discussion (Misc queries) | |||
Excel 2003. Custom format gets replaced by Special format. | New Users to Excel | |||
2003 - 2007 custom macro and custom button restore. | Excel Discussion (Misc queries) | |||
Custom toolbars and Excel 2003 - 2007 portability | Excel Programming | |||
Custom Number Format Excel 2003 | Excel Programming |