+ Reply to Thread
Results 1 to 4 of 4

Pivoting times clumps hours, only displays minutes.

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Pivoting times clumps hours, only displays minutes.

    I have a pivot of times, 8:00, 8:15, 9:00, 9:30, etc.... My pivot table groups them as 8 & 9, great, but when you expand the section instead of seeing 8:00 or 8:15, you just see :00, :15.

    Is there way to get it to list the full time?

  2. #2
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Pivoting times clumps hours, only displays minutes.

    I was unable to reproduce your error, so please attach a sample workbook

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
    2. Make sure that your desired solution is also shown (mock up the results manually).
    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  3. #3
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    Re: Pivoting times clumps hours, only displays minutes.

    OK. I uploaded a workbook. First sheet is the pivot table with a note showing what I am referring to. Sheets 2/3 can be ignored. Sheet 4 is the original data.

    Trying to stop it from showing time as

    11 AM
    + :10

    and instead show

    11 AM
    + 11:10
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Pivoting times clumps hours, only displays minutes.

    You had grouped the field PUNCH into Minutes and Hours, thus creating a new field titled HOURS2.

    - HOURS2 represents the hours
    - PUNCH represents the minutes.


    To fix this - on the pivot table, right click on the PUNCH field and select UNGROUP

    Update:
    Quote Originally Posted by taylorsm View Post
    Trying to stop it from showing time as

    11 AM
    + :10

    and instead show

    11 AM
    + 11:10
    The info above stands as-is but, if you want to achieve the 11 AM & 11:10, then I suggest that you duplicate the PUNCH field and call it PUNCH HOUR
    Next you could do the group set but just pick hour.

    See attachment
    Attached Files Attached Files
    Last edited by Syrkrasi; 11-16-2017 at 03:51 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Timeline or Gantt Chart with times by minutes/hours rather than days
    By dvs in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 01-13-2021, 09:53 AM
  2. Changing Hours and Minutes to actual times
    By scarames in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-22-2014, 11:44 PM
  3. [SOLVED] Formula where the result (which contains a decimal) displays as hours and minutes
    By bridge4444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2014, 07:38 AM
  4. Calculate hours and minutes between two dates/times
    By farrellao in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-17-2014, 07:02 AM
  5. addition of times in hours minutes and seconds
    By zulfiqar in forum Excel General
    Replies: 1
    Last Post: 01-31-2010, 03:29 AM
  6. Calculating difference in times as hours and minutes
    By Andrea112233 in forum Excel General
    Replies: 3
    Last Post: 08-10-2009, 10:23 AM
  7. Replies: 7
    Last Post: 08-15-2007, 08:33 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1