1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: excel 2010 Pivot Table error

Discussion in 'Business Applications' started by SlowHnds, Apr 11, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    I have a pivot table that displays totals for month. It is an eating diary I downloaded off the web

    Everything was fine until 10 April. the pivot table put 10 Apr

    Pivot tables do weird things with dates anyway displaying them how Excel decides to not how I have them formatted. ie. ddd mmm dd, yyyy

    Attached is a snip of what has happened.

    and what it looks like in the data
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    can you load the actual / dummy data spreadsheet , as that does not look like its sorting correctly by date - but by text
    hence the 1 firsts
     
  3. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    The pivot table does it's own thing and I do believe it is changing my date format to text. I can't prove it but I think that's part of what is happening

    The data is in the right and correct format in my table. I can format it to display to any date style I want and it sorts correctly.

    The pivot table dates can't be changed or reformatted to show differently.

    I have been able to redo the pivot table. Again the dates are in a different format, but at least they are in order now.

    I just would like to know why the pivot table changes data formats and will not allow me to format them back.

    ou can get the original Excel file at http://excel-example.com/templates/calories-carbohydrates-proteins-fats-fiber-diary-excel#more-693 The template is at the bottom of the page. It contains some dummy data so no secrets are revealed.

    For those that need or want to keep a food journal it's the best I've found. At first it's time consuming as one needs to add in a lot of foods to the lists. Once you've done that though it gets easy. I'd like it better if it was in an Access database but I'm not smart enough to build it.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    the template with sample data has dates entered

    we would need to see your sheet to make sure you have entered dates
    and not changed to text
    click on your diary and the date and then look at format
    change to general
    and you should just see a number
    42108 is 14/4/15
     
  5. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    I've tried everything I can think of and the whole pivot table is off.

    Attached is a Dummy data this is in no way how I eat. Note the total on the over under is so far off it's nuts.

    I'm getting to like Excel less and less everytime I try to work this pivot table. I've recreated it like 8 times
     

    Attached Files:

  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    what do you want on the pivot table

    I have created one by month and then totals at the bottom
    seems to be correct

    is that any good ?
     

    Attached Files:

  7. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    This is what I'm looking for but again I don't know how I got it. and it isn't my real information I was just playing with it recreating it yet again. It worked right but still and yet dates shown on the pivot table are not as they are on the information table.

    Pivot tables seem to do what they want and it's not the same way twice in a row.
     

    Attached Files:

  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    well it does vary depending on how you are setting up and grouping

    can you be specific the dates seem to be working ok

    is it just the format of the date that you are looking for ?
    not sure how to format dates that have been grouped in a pivot table

    The rows are grouping data together
    then in the values - you put the fields you want to be grouped
    thne you choose how to handle those values
    SUM, Count, etc
     
  9. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    it is the format of the date in the pivot table now. I'll know more when I get home and try to redo the stats there. That it worked on a dummy set of data was a fluke. As I've tried over a dozen times and couldn't get it to group correctly or had wrong totals etc and so on. Pivot tables seem to make their own rules.
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    can we see the real data - you can always send to me offline , if you dont want on a public forum
    i will PM my email address

    do not put email address into forums

    we will otherwise keep all communication via this thread and NOT offline

    if you happy to load the real data, onto a public site - then attach the sample here
     
  11. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    I've given up on the pivot table. From totals being out of whack to losing formatting when new rows are added and the pivot table is refreshed.

    It's just not worth the bother.

    I'll put a total line at the end of each month and live with that.

    Not solved I'm just done with it.
     
  12. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    OK, as you wish
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1146426

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice