Forum Discussion
Solved
Date Formatting
I wonder if anyone could help me please? Its probably really simple but I can't for the life of me get Excel to do as I would like. On all our company graphs/pivots etc, date is displayed as ...
- In your file, the value in A2 ia not a true date, but in fact text. But as soon as you edit that cell, Excel will try to assess what you entered, recognize the entry as a date and enter a true date into the cell. To avoid all of this, before making ANY edits to your data, Format the column in question as Text. Then do your edits.
What does it do for your pivots that you do not like precisely?
Rather than having one entry per month, it creates one column per date, I inherited the spreadsheet, so older months are actually in YY/MM when I copy that down and change the month in the formatting bar, it goes straight to full date. Even though that column is custom formatted YY/MM it still puts new entries to full date (in the ribbon even though displaying in the actual data as YY/MM) so the custom formatting changes the display but not wherever it pulls from and display in the ribbon.
I have attached (I hope) an example of my spreadsheet which I hope will help explain what I mean
Thank you
Clair
- In your file, the value in A2 ia not a true date, but in fact text. But as soon as you edit that cell, Excel will try to assess what you entered, recognize the entry as a date and enter a true date into the cell. To avoid all of this, before making ANY edits to your data, Format the column in question as Text. Then do your edits.
- I had the same issue and this worked. Is it a glitch in the new excel?
- No this problem has been around for years and is due to how data is entered or imported into Excel. If the source of the data is text, Excel will mark the values as text. So you need a way to convert the values to dates before creating pivottables.
Thank you x a million.:-)
how do I flag my thread as resolved please?
Thank you
Clair
