Forum Discussion
Date Formatting
- 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.
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
- 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.
Hi
i have a similar problem however converting my column to text didn't help. In the cell the date is displayed thus:
05-Oct-1980 On the ribbon the date is displayed thus:
10/5/1980
I am trying to strip out the year from the dob so I can calculate the age.
Please help,
Catherine
Thank you x a million.:-)
how do I flag my thread as resolved please?
Thank you
Clair
- I don't think you can, at least I could not find a way to do so.
