We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Excel 2007 and Time/Duration formats

Medium Priority
5,172 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

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.
^^^  "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.

Author

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
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.