Excel 2016 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

05-07-2018 08-32-35.jpg


It causes havoc for my pivots/filters.


How can I have the column format my dates as YY/MM to display in the data and in the display bar?

Please? What is the correct name for the display bar please?

Thank you, Clair

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Have a look at this link - it explains it very well - https://www.extendoffice.com/documen...mat.html#chart

Right-click on axis \ select appropriate options (dependent on whether a pivot chart or normal chart) \ similar to cell number (date) format

Sorry thought picture would be OK, here is a section of the spreadsheet.

Although the column is custom formatted to YY/MM, it displays that way yet in the view bar it shows as a date.

I would like to be able to format the entire column as YY/MM so the full date is eliminated. I entre data each month. Graphs/pivots are then linked to the data by a named range.

Clair

Why do you want values like 18/07 instead of real dates?

With values like that you won't be able to sort or group properly and it could affect date based calculations.

I think the pertinent question is in what way is it wreaking havoc with your pivot tables? Explain, please.

Yes I would like them to display solely YY/MM, it does group them by 18/01, 18/02 etc, but unless I can make new data added in the same format, it would pivot/graph 18/01, 18/02 then miss out for instance 18/06 01/06/2018 as they are not formatted according to all the others months/years in the filer (only display a rolling 12 months) I would like to know how to default the entire column to YY/MM with no reference to a full date anywhere, so I do it properly. So each month it does it automatically.

I'm being abit thick aren't I?

Clair

Does this help. I have entered some dummy data for 18/08.

Even though that entire column is formatted - custom YY/MM it is still separating them out as the full date in the pivot (graph data) and the graph.

If I am actually doing something wrong, I would like to fix now, so I don't face this every month.

Does this help you see where I'm coming from?

Hope so
Thank you
Clair

Clair

If you use full dates throughout you should be able to group things as you want in your pivot tables and graphs.

The formatting isn't what matters when grouping/sorting, it's the actual underlying values.

See the attached file.

Hi

I have now set 2 entire periods (we use periods here not calendar months) the entire company (manufacturing (me), purchasing, sales, finance, etc) all display data by yy/mm so it displays the periods for a year (November-November for us so Period 1 is actually the December - we are now in July which is period 8.

Does that help with the full date being visible isn't the way for me to go.

The data could be 01/08/2018 but I would need to be able to change it to read yy/mm in the pivots which would feed through to the chart.

I know how to change row of column titles but not the data itself. Some graphs get printed each month and displayed in a board displayed in the hall way.

Thank you
Clair

Wonderful Norie, how did you change the period title's on the pivot please?

as in my spreadsheet, I have now changed 3 full periods to full date, the entire column is auto formatted yy/mm and I have refreshed but they are still coming up in my pivot and chart as full month dates?

If I right click and go to field settings it displays Period() which is the row title?

Getting there thank you for your patience

Clair

fixed it, formatted the column as text, and it has pulled the yy/mm (periods) through :-)