Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Clock Time Vs. Durations in Excel

  • 18-05-2011 10:09am
    #1
    StompToWork
    Registered Users, Registered Users 2 Posts: 905 ✭✭✭


    Okay, I'm tearing my hair out!! I am trying to do some calcs on durations of calls to my helpdesk. I have an export from my phone system, and the call durations are in the cells as hh:mm:ss (eg 00:15:02). However, in the formula bar, no matter what time format I select, while it might show correctly in the cell, it still shows up in Clock format in the Formula bar (eg 12:15:02 AM).

    This is proving a hassle, because when I try an put a pivot table together, it is constantly coming up in Clock format, when I want to be able to group by durations (>2min, >5min etc)

    Any suggestions?


Comments

  • #2
    FSL
    Registered Users, Registered Users 2 Posts: 1,456 ✭✭✭FSL


    Could your phone system just return the value in seconds.

    You would then have no problem with Excel deciding it was a time cell. You could have a second cell showing number of seconds as hh:mm:ss but use the absolute value in seconds for your pivot table.


  • #3
    StompToWork
    Registered Users, Registered Users 2 Posts: 905 ✭✭✭StompToWork


    FSL wrote: »
    Could your phone system just return the value in seconds.

    You would then have no problem with Excel deciding it was a time cell. You could have a second cell showing number of seconds as hh:mm:ss but use the absolute value in seconds for your pivot table.

    I thought about your first point and queried the phone system, but no joy there. Regarding breaking into separate minutes and seconds, hadn't thought of that, but might give it a go.

    What I ended up doing, which seems to have worked relatively well is take advantage of the fact that because none of the lengths exceeded 60 minutes (00:59:59), I simply grouped by minutes. Since Excel thought all the times were during 12am, grouping by minutes gave me the data I needed.

    Still, it was quite frustrating, and if I ever get a duration of > 1 hour, that method will not work anymore.


  • #4
    Mr November
    Registered Users, Registered Users 2 Posts: 60 ✭✭Mr November


    That should be quite simple - are you using a formula on the time values before creating the pivot? If so, can you not do these calculations on the pivot?

    In the calculated column fields on the pivot you just need to change the format - Field Settings - Number - Time - hh:mm:ss


Advertisement