I have table with months in columna A: January, February, March…,
And values in column B: 13, 14, 18, etc.
I would like to create Pivot Table with timeline slicer.
If I use full date 01-01-2016 there will not be any problem with this, but how to create that when I have only Months? I need to format these cells as Date, but how?
Suggest using an auxiliary column for months.
what do you mean?
Upload a sample file, I show how to using an auxiliary column for months.
I have Pivot Table basen on months and values but I cannot add Timeline couse of field is not formatted as date.
How to format it?
At #1 you say use full date quarry is OK, but now your sample file data were not date.
I made a sample file for you.
ikboy,
At #1 I asked about case when I do not have full data (maybe my english misled a little).
I have value for whole month so I do not have each day mentioned and I have to create chart for months.
Any ideas?
You can still use ikboy's suggestion of adding a help column, which is a true date.
See attached modification to your example file.
Thanks for it Andy Pope,
This is very good but I cannot make it by myself.
I have written the same formula:
and changed cell formating for customs "mmmm rrrr" and does not work.
Please Login or Register to view this content.
Even If I recalculate your formula in file I get error #VALUE
How it is work?
And why you addes "1 " before month?
I thing like this you can easy understand:
Formula:
Please Login or Register to view this content.
but why it does not work when I do this ?
You are using rrrr for years when it should probably by yyyy
this could be a localisation issue with English versus Polish language pack.
Either way the solution to your problem is that in order to use the date slicer you need a column of data that is an actual date.
ok,
now works formula:
when cell M33 contains "Luty 2016" ("Luty" is "February" in polish),Please Login or Register to view this content.
so it means that I have some settings set to polish. Where I can find it?
All Excel (menus, formulas, etc.) is in english.
Is any way to reach this if I have text in english like "January".
Still do not understand why you have added "1 " before month earlier
Last edited by zico8; 10-22-2016 at 07:33 AM.
the 1 was added to make the textual date more complete in order to help the function determine that the text was in fact a date.
Seems the function is robust enough to work with or without it.
I would look in File > Options > Language
It may also be a Windows setting of some sort
Can you help me to find these settings in excel or system ?
I do not know where the settings are exactly.
Perhaps you can add the locale code to the TEXT function. This should put it out in english. You may need to change yyyy to rrrr
=TEXT(D4,"[$-409]mmmm yyyy")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks