Excel 2007 and Time/Duration formats

Posted on 2009-02-12
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)
Question by:ctna
    LVL 4

    Expert Comment

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

    Expert Comment

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

    Author Comment

    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
    LVL 1

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now