Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5144
  • Last Modified:

Excel 2007 and Time/Duration formats

I am exporting an .xml out of our call accounting application. Prior to using Office 2007, everything was fine. In 2007 the way the duration is formatted is incorrect. I can view the data from a PC with Excel 2003 and it is good. I am not doing any calculations with times or duration of time, just charting data. The duration of time should be in this format 0:15:37 which is 0 hours, 15 min, 37 secs. In Excel 2007 it is displaying as 240:15:37 and in the formula bar it is displaying as "1/10/1900  12:07:04 AM". This is not a point in time, it is a counter, a stopwatch so to say. I have tried all available formatting options for the cells and although some appear to be correct, the data is being maniulated wrong. Example, what should be 0:15:37 displays as 240:15:37 (I can choose custom, h:mm:ss and it corrects itself). Now the fun part, if the hour value is greater than 0, I have a problem. What should be 44:34:02 displays correctly by default. If I choose the (custom, h:mm:ss format) it now displays as 20:34:02. So, here is the final issue, if my hour value is 0, excel 2007 displays the hour value as 240 by default. If the hour value is greater than 0, excel 2007 displays correctly by default (to an extent, see attachments). I should not have to sort my values and selectively set formatting on certain cells. Anybody know how to fix this? I have attached snips from 2003 and 2007, also I snipped the cell formatting from each (they are formatted the same)
excel-2007-snip.JPG
excel-2003-snip.JPG
excel-2003-formatting.JPG
excel-2007-formatting.JPG
0
ctna
Asked:
ctna
  • 2
  • 2
1 Solution
 
josh-grayCommented:
Under Formatting, choose 'Time' and then it would be the 7th one down I believe.
'37:30:55'  Which is an 'hour:minutes:seconds' counter.

Hope this is what you're looking for.
0
 
josh-grayCommented:
^^^  "Which is an 'hour:minutes:seconds' counter"  was not entirely correct.  It is still a time format, but gives you the same results as the 2003 formatting from your screenshot.

I've verified it works on 0 hours and more than 24 hours also on my excel 2007.
0
 
ctnaAuthor Commented:
Thanks for the help but that is not working. I've attached another snip to show that when doing that (posted in comment above), it does not change. Look at my attachment, the "SAMPLE" shows how the formatting change will display my data and it is not different from the original. Josh, you said you verified that it works, I wonder why it is not working on mine? In my example, it should display as 18:38:43
2007-format-attempt.JPG
0
 
ctnaAuthor Commented:
The problem ended up being the call accounting software. I guess the way it was exporting to XML was causing the issue. They had a patch for it, although it was not posted in their knowledge base.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now