Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Clock Time Vs. Durations in Excel

  • 18-05-2011 11: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: 64 ✭✭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