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 18/06 (yy/mm) I have tried using a custom format but it always defaults to a full date 01/06/2018 in the display bar even though displaying correctly in the data
It causes havoc for my pivots.
How can I have the column format my dates as YY/MM to display in the data and in the display bar?
Please?
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.
- 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.
