Solved: excel 2010 Pivot Table error

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
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
 

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.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
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
 

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
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
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 ?
 

Attachments

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.
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
well it does vary depending on how you are setting up and grouping

It worked right but still and yet dates shown on the pivot table are not as they are on the information table.
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
 

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.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,484
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
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top