Date Formatting

[Clair Huntley]

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

[#2]

What does it do for your pivots that you do not like precisely?

[#3]

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

[#4 · Accepted solution]

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.

[#5]

Thank you x a million.:-)

[#6]

how do I flag my thread as resolved please?

Thank you

Clair

[#7]

I don't think you can, at least I could not find a way to do so.

[#8]

I had the same issue and this worked. Is it a glitch in the new excel?

[#9]

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.

[#10]

@Jan Karel Pieterse 

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

[#11]

A simple way is to use the YEAR function in another cell. To calculate the age you can also use the DATEDIF function as shown here: https://support.office.com/en-us/article/DATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c