Excel - pivot table does not group dates
I am trying to group the dates in the pivot table into months and years. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script.
For some reason when I create a pivot table from this raw table I am getting an error message saying "Cannot group this selection". I checked I don't have any blank data, the column is in date format. I tried everything, tried to convert from test -to -columns to dates, that in turn brings an error. There is something really wrong with it - not sure what.
Can someone help me, please?
More clarification:
- My Raw Data is a table that is refreshable thru embedded SQL script that has a column with dates - let's say it's column A.
- This column A that is populated from SQL has dates in the format 01/20/2016 and also has blanks. I am doing few steps thru formulas to create another column - column B. As i need to return the Sunday of that specific date - for example if the date is saying 9/20/2016 - column B will return 09/18/2016.
Bascially Column B has the dates equal to the dates in column A, and whatever is blank in column A is #N/A in column B. - I am creating column C to grab the dates from column B in the following way: Column C=IFERROR(COLUMN B,"01/01/2018"). So whatever in #N/A in column B I am saying let it be equal to "01/01/2018".
- I am creating a pivot table from this raw data table and dragging the column C as it's mu date column. Then I want to group these dates into months and years as well. However I am either getting the error "Cannot group this selection" or if it is grouping it is creating another column called Group 1 which is not what i am looking for, don't know if it can be converted to months or years?
on the home tab there is a [Group Selection] section which is greyed out. I think if it is not greyed out it will be able to give me the option of choosing month and year hierarchy.
I checked column c ( my date column) there are no blanks - the format it 1/2/2016, don't know why it's not grouping.
I tried [Text to Columns] within pivot table, got an error. I tried [Text to Columns] in the raw data table it is converting columnn C into it its actual formulas: Iferror
microsoft-excel microsoft-excel-2016
edited Oct 30 '16 at 8:14
3498DB
15.7k114762
asked Sep 19 '16 at 0:43
Nane Amiryan
16114
add a comment |
I am trying to group the dates in the pivot table into months and years. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script.
For some reason when I create a pivot table from this raw table I am getting an error message saying "Cannot group this selection". I checked I don't have any blank data, the column is in date format. I tried everything, tried to convert from test -to -columns to dates, that in turn brings an error. There is something really wrong with it - not sure what.
Can someone help me, please?
More clarification:
- My Raw Data is a table that is refreshable thru embedded SQL script that has a column with dates - let's say it's column A.
- This column A that is populated from SQL has dates in the format 01/20/2016 and also has blanks. I am doing few steps thru formulas to create another column - column B. As i need to return the Sunday of that specific date - for example if the date is saying 9/20/2016 - column B will return 09/18/2016.
Bascially Column B has the dates equal to the dates in column A, and whatever is blank in column A is #N/A in column B. - I am creating column C to grab the dates from column B in the following way: Column C=IFERROR(COLUMN B,"01/01/2018"). So whatever in #N/A in column B I am saying let it be equal to "01/01/2018".
- I am creating a pivot table from this raw data table and dragging the column C as it's mu date column. Then I want to group these dates into months and years as well. However I am either getting the error "Cannot group this selection" or if it is grouping it is creating another column called Group 1 which is not what i am looking for, don't know if it can be converted to months or years?
on the home tab there is a [Group Selection] section which is greyed out. I think if it is not greyed out it will be able to give me the option of choosing month and year hierarchy.
I checked column c ( my date column) there are no blanks - the format it 1/2/2016, don't know why it's not grouping.
I tried [Text to Columns] within pivot table, got an error. I tried [Text to Columns] in the raw data table it is converting columnn C into it its actual formulas: Iferror
microsoft-excel microsoft-excel-2016
edited Oct 30 '16 at 8:14
3498DB
15.7k114762
asked Sep 19 '16 at 0:43
Nane Amiryan
16114
Try turning the column to "General". If the dates change to number then you should be able to group. If they remain dates then they are text strings that look like dates and not true dates in Excel.
– Scott Craner
Sep 19 '16 at 0:49
Thanks! I tried converting them into general - most of them got converted to numbers, but 5 of them remained as dates. ANother issue I tried grouping the ones that were converted to numbers and instead of bringing a hierarchy of month and year it just gives me Group one with the same filed title. Do you know why? The group selection field is greyed out on the home tab
– Nane Amiryan
Sep 19 '16 at 2:34
add a comment |
I am trying to group the dates in the pivot table into months and years. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script.
For some reason when I create a pivot table from this raw table I am getting an error message saying "Cannot group this selection". I checked I don't have any blank data, the column is in date format. I tried everything, tried to convert from test -to -columns to dates, that in turn brings an error. There is something really wrong with it - not sure what.
Can someone help me, please?
More clarification:
- My Raw Data is a table that is refreshable thru embedded SQL script that has a column with dates - let's say it's column A.
- This column A that is populated from SQL has dates in the format 01/20/2016 and also has blanks. I am doing few steps thru formulas to create another column - column B. As i need to return the Sunday of that specific date - for example if the date is saying 9/20/2016 - column B will return 09/18/2016.
Bascially Column B has the dates equal to the dates in column A, and whatever is blank in column A is #N/A in column B. - I am creating column C to grab the dates from column B in the following way: Column C=IFERROR(COLUMN B,"01/01/2018"). So whatever in #N/A in column B I am saying let it be equal to "01/01/2018".
- I am creating a pivot table from this raw data table and dragging the column C as it's mu date column. Then I want to group these dates into months and years as well. However I am either getting the error "Cannot group this selection" or if it is grouping it is creating another column called Group 1 which is not what i am looking for, don't know if it can be converted to months or years?
on the home tab there is a [Group Selection] section which is greyed out. I think if it is not greyed out it will be able to give me the option of choosing month and year hierarchy.
I checked column c ( my date column) there are no blanks - the format it 1/2/2016, don't know why it's not grouping.
I tried [Text to Columns] within pivot table, got an error. I tried [Text to Columns] in the raw data table it is converting columnn C into it its actual formulas: Iferror
microsoft-excel microsoft-excel-2016
edited Oct 30 '16 at 8:14
3498DB
15.7k114762
asked Sep 19 '16 at 0:43
Nane Amiryan
16114
I am trying to group the dates in the pivot table into months and years. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script.
For some reason when I create a pivot table from this raw table I am getting an error message saying "Cannot group this selection". I checked I don't have any blank data, the column is in date format. I tried everything, tried to convert from test -to -columns to dates, that in turn brings an error. There is something really wrong with it - not sure what.
Can someone help me, please?
More clarification:
- My Raw Data is a table that is refreshable thru embedded SQL script that has a column with dates - let's say it's column A.
- This column A that is populated from SQL has dates in the format 01/20/2016 and also has blanks. I am doing few steps thru formulas to create another column - column B. As i need to return the Sunday of that specific date - for example if the date is saying 9/20/2016 - column B will return 09/18/2016.
Bascially Column B has the dates equal to the dates in column A, and whatever is blank in column A is #N/A in column B. - I am creating column C to grab the dates from column B in the following way: Column C=IFERROR(COLUMN B,"01/01/2018"). So whatever in #N/A in column B I am saying let it be equal to "01/01/2018".
- I am creating a pivot table from this raw data table and dragging the column C as it's mu date column. Then I want to group these dates into months and years as well. However I am either getting the error "Cannot group this selection" or if it is grouping it is creating another column called Group 1 which is not what i am looking for, don't know if it can be converted to months or years?
on the home tab there is a [Group Selection] section which is greyed out. I think if it is not greyed out it will be able to give me the option of choosing month and year hierarchy.
I checked column c ( my date column) there are no blanks - the format it 1/2/2016, don't know why it's not grouping.
I tried [Text to Columns] within pivot table, got an error. I tried [Text to Columns] in the raw data table it is converting columnn C into it its actual formulas: Iferror
microsoft-excel microsoft-excel-2016
microsoft-excel microsoft-excel-2016
edited Oct 30 '16 at 8:14
3498DB
15.7k114762
asked Sep 19 '16 at 0:43
Nane Amiryan
16114
edited Oct 30 '16 at 8:14
3498DB
15.7k114762
asked Sep 19 '16 at 0:43
Nane Amiryan
16114
edited Oct 30 '16 at 8:14
3498DB
15.7k114762
edited Oct 30 '16 at 8:14
3498DB
15.7k114762
edited Oct 30 '16 at 8:14
3498DB
15.7k114762
15.7k114762
asked Sep 19 '16 at 0:43
Nane Amiryan
16114
asked Sep 19 '16 at 0:43
Nane Amiryan
16114
asked Sep 19 '16 at 0:43
Nane Amiryan
16114
16114
Try turning the column to "General". If the dates change to number then you should be able to group. If they remain dates then they are text strings that look like dates and not true dates in Excel.
– Scott Craner
Sep 19 '16 at 0:49
Thanks! I tried converting them into general - most of them got converted to numbers, but 5 of them remained as dates. ANother issue I tried grouping the ones that were converted to numbers and instead of bringing a hierarchy of month and year it just gives me Group one with the same filed title. Do you know why? The group selection field is greyed out on the home tab
– Nane Amiryan
Sep 19 '16 at 2:34
add a comment |
Try turning the column to "General". If the dates change to number then you should be able to group. If they remain dates then they are text strings that look like dates and not true dates in Excel.
– Scott Craner
Sep 19 '16 at 0:49
Thanks! I tried converting them into general - most of them got converted to numbers, but 5 of them remained as dates. ANother issue I tried grouping the ones that were converted to numbers and instead of bringing a hierarchy of month and year it just gives me Group one with the same filed title. Do you know why? The group selection field is greyed out on the home tab
– Nane Amiryan
Sep 19 '16 at 2:34
Try turning the column to "General". If the dates change to number then you should be able to group. If they remain dates then they are text strings that look like dates and not true dates in Excel.
– Scott Craner
Sep 19 '16 at 0:49
Try turning the column to "General". If the dates change to number then you should be able to group. If they remain dates then they are text strings that look like dates and not true dates in Excel.
– Scott Craner
Sep 19 '16 at 0:49
Thanks! I tried converting them into general - most of them got converted to numbers, but 5 of them remained as dates. ANother issue I tried grouping the ones that were converted to numbers and instead of bringing a hierarchy of month and year it just gives me Group one with the same filed title. Do you know why? The group selection field is greyed out on the home tab
– Nane Amiryan
Sep 19 '16 at 2:34
Thanks! I tried converting them into general - most of them got converted to numbers, but 5 of them remained as dates. ANother issue I tried grouping the ones that were converted to numbers and instead of bringing a hierarchy of month and year it just gives me Group one with the same filed title. Do you know why? The group selection field is greyed out on the home tab
– Nane Amiryan
Sep 19 '16 at 2:34
add a comment |
2 Answers
2
active
oldest
votes
There may be a locale issue. When dates are stored as text, converted to dates will work fine if the date is 1/1/2016, but will not properly convert all dates if your locale uses DMY and the text date is using MDY (or the other way around). Test a cell in your data source that has a day value greater than 12 and ensure that it is returned as a proper date.
After checking, turn your data source into an Excel Table Object with Ctrl-T or Insert > Table. Then use the filter drop-down on the date column. If the column contains only dates, you should not see individual entries, but a year/month/day expandable tree.
Also, double check for blanks. Maybe your pivot data source includes a blank row at the end of the source data.
Edit: If you get an error when doing text-to-columns to dates, then that is a clear indicator that the text cannot be interpreted as a date. In the Text to column wizard, ensure that you set the correct order of day, month and year, as it is in the source. In the screenshot you can see that the date is in the order MDY. My locale uses DMY by default, so I need to let the text to column wizard know that the source data is in MDY order.
Edit2: From your comments it looks like you are using a formula to construct the date. A formula cannot be processed by text to columns (why on earth would you do that????).
Ensure that the formula you use to create the dates returns real dates, not text that looks like a date. Edit your question, provide the details of the formula for inspection. And -- please, hold off with the comments. This is not a chatty forum.
answered Sep 19 '16 at 1:29
teylyn
16.9k22539
So I am trying to apply [Text to Columns] and then MDY format, after that the date in a cell dissappears and the formula is displayed. My original date column A consists of the following:
– Nane Amiryan
Sep 19 '16 at 2:22
1. Dates in the format (9/20/2016)
– Nane Amiryan
Sep 19 '16 at 2:23
2. #N/A In order to not have #N/As in my dates column i add a second column B where I add a formula: =Iferror(cell from column A, "01/01/2018"
– Nane Amiryan
Sep 19 '16 at 2:25
Basically I am denoting every #N/A as 01/01/2018 in column. When I create the pivot table i drag column B for the date field
– Nane Amiryan
Sep 19 '16 at 2:26
Can it be that column B is a formula that's why it's not working? [Text to Column] when applying inside pivot table brings an error, when I apply in the be raw table before pivoting, it converts column B into it's formulas
– Nane Amiryan
Sep 19 '16 at 2:27
|
show 5 more comments
Your PivotTable is interpreting "01/01/2018" as text rather than as a date.
Instead ofIFERROR(COLUMN B,"01/01/2018"), useIFERROR(COLUMN B,DATE(2018,1,1)).
answered Aug 18 '17 at 17:07
Dan Henderson
6521718
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1125762%2fexcel-pivot-table-does-not-group-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
There may be a locale issue. When dates are stored as text, converted to dates will work fine if the date is 1/1/2016, but will not properly convert all dates if your locale uses DMY and the text date is using MDY (or the other way around). Test a cell in your data source that has a day value greater than 12 and ensure that it is returned as a proper date.
After checking, turn your data source into an Excel Table Object with Ctrl-T or Insert > Table. Then use the filter drop-down on the date column. If the column contains only dates, you should not see individual entries, but a year/month/day expandable tree.
Also, double check for blanks. Maybe your pivot data source includes a blank row at the end of the source data.
Edit: If you get an error when doing text-to-columns to dates, then that is a clear indicator that the text cannot be interpreted as a date. In the Text to column wizard, ensure that you set the correct order of day, month and year, as it is in the source. In the screenshot you can see that the date is in the order MDY. My locale uses DMY by default, so I need to let the text to column wizard know that the source data is in MDY order.
Edit2: From your comments it looks like you are using a formula to construct the date. A formula cannot be processed by text to columns (why on earth would you do that????).
Ensure that the formula you use to create the dates returns real dates, not text that looks like a date. Edit your question, provide the details of the formula for inspection. And -- please, hold off with the comments. This is not a chatty forum.
answered Sep 19 '16 at 1:29
teylyn
16.9k22539
So I am trying to apply [Text to Columns] and then MDY format, after that the date in a cell dissappears and the formula is displayed. My original date column A consists of the following:
– Nane Amiryan
Sep 19 '16 at 2:22
1. Dates in the format (9/20/2016)
– Nane Amiryan
Sep 19 '16 at 2:23
2. #N/A In order to not have #N/As in my dates column i add a second column B where I add a formula: =Iferror(cell from column A, "01/01/2018"
– Nane Amiryan
Sep 19 '16 at 2:25
Basically I am denoting every #N/A as 01/01/2018 in column. When I create the pivot table i drag column B for the date field
– Nane Amiryan
Sep 19 '16 at 2:26
Can it be that column B is a formula that's why it's not working? [Text to Column] when applying inside pivot table brings an error, when I apply in the be raw table before pivoting, it converts column B into it's formulas
– Nane Amiryan
Sep 19 '16 at 2:27
|
show 5 more comments
There may be a locale issue. When dates are stored as text, converted to dates will work fine if the date is 1/1/2016, but will not properly convert all dates if your locale uses DMY and the text date is using MDY (or the other way around). Test a cell in your data source that has a day value greater than 12 and ensure that it is returned as a proper date.
After checking, turn your data source into an Excel Table Object with Ctrl-T or Insert > Table. Then use the filter drop-down on the date column. If the column contains only dates, you should not see individual entries, but a year/month/day expandable tree.
Also, double check for blanks. Maybe your pivot data source includes a blank row at the end of the source data.
Edit: If you get an error when doing text-to-columns to dates, then that is a clear indicator that the text cannot be interpreted as a date. In the Text to column wizard, ensure that you set the correct order of day, month and year, as it is in the source. In the screenshot you can see that the date is in the order MDY. My locale uses DMY by default, so I need to let the text to column wizard know that the source data is in MDY order.
Edit2: From your comments it looks like you are using a formula to construct the date. A formula cannot be processed by text to columns (why on earth would you do that????).
Ensure that the formula you use to create the dates returns real dates, not text that looks like a date. Edit your question, provide the details of the formula for inspection. And -- please, hold off with the comments. This is not a chatty forum.
answered Sep 19 '16 at 1:29
teylyn
16.9k22539
So I am trying to apply [Text to Columns] and then MDY format, after that the date in a cell dissappears and the formula is displayed. My original date column A consists of the following:
– Nane Amiryan
Sep 19 '16 at 2:22
1. Dates in the format (9/20/2016)
– Nane Amiryan
Sep 19 '16 at 2:23
2. #N/A In order to not have #N/As in my dates column i add a second column B where I add a formula: =Iferror(cell from column A, "01/01/2018"
– Nane Amiryan
Sep 19 '16 at 2:25
Basically I am denoting every #N/A as 01/01/2018 in column. When I create the pivot table i drag column B for the date field
– Nane Amiryan
Sep 19 '16 at 2:26
Can it be that column B is a formula that's why it's not working? [Text to Column] when applying inside pivot table brings an error, when I apply in the be raw table before pivoting, it converts column B into it's formulas
– Nane Amiryan
Sep 19 '16 at 2:27
|
show 5 more comments
There may be a locale issue. When dates are stored as text, converted to dates will work fine if the date is 1/1/2016, but will not properly convert all dates if your locale uses DMY and the text date is using MDY (or the other way around). Test a cell in your data source that has a day value greater than 12 and ensure that it is returned as a proper date.
After checking, turn your data source into an Excel Table Object with Ctrl-T or Insert > Table. Then use the filter drop-down on the date column. If the column contains only dates, you should not see individual entries, but a year/month/day expandable tree.
Also, double check for blanks. Maybe your pivot data source includes a blank row at the end of the source data.
Edit: If you get an error when doing text-to-columns to dates, then that is a clear indicator that the text cannot be interpreted as a date. In the Text to column wizard, ensure that you set the correct order of day, month and year, as it is in the source. In the screenshot you can see that the date is in the order MDY. My locale uses DMY by default, so I need to let the text to column wizard know that the source data is in MDY order.
Edit2: From your comments it looks like you are using a formula to construct the date. A formula cannot be processed by text to columns (why on earth would you do that????).
Ensure that the formula you use to create the dates returns real dates, not text that looks like a date. Edit your question, provide the details of the formula for inspection. And -- please, hold off with the comments. This is not a chatty forum.
answered Sep 19 '16 at 1:29
teylyn
16.9k22539
There may be a locale issue. When dates are stored as text, converted to dates will work fine if the date is 1/1/2016, but will not properly convert all dates if your locale uses DMY and the text date is using MDY (or the other way around). Test a cell in your data source that has a day value greater than 12 and ensure that it is returned as a proper date.
After checking, turn your data source into an Excel Table Object with Ctrl-T or Insert > Table. Then use the filter drop-down on the date column. If the column contains only dates, you should not see individual entries, but a year/month/day expandable tree.
Also, double check for blanks. Maybe your pivot data source includes a blank row at the end of the source data.
Edit: If you get an error when doing text-to-columns to dates, then that is a clear indicator that the text cannot be interpreted as a date. In the Text to column wizard, ensure that you set the correct order of day, month and year, as it is in the source. In the screenshot you can see that the date is in the order MDY. My locale uses DMY by default, so I need to let the text to column wizard know that the source data is in MDY order.
Edit2: From your comments it looks like you are using a formula to construct the date. A formula cannot be processed by text to columns (why on earth would you do that????).
Ensure that the formula you use to create the dates returns real dates, not text that looks like a date. Edit your question, provide the details of the formula for inspection. And -- please, hold off with the comments. This is not a chatty forum.
answered Sep 19 '16 at 1:29
teylyn
16.9k22539
edited Sep 19 '16 at 3:05
answered Sep 19 '16 at 1:29
teylyn
16.9k22539
answered Sep 19 '16 at 1:29
teylyn
16.9k22539
answered Sep 19 '16 at 1:29
teylyn
16.9k22539
16.9k22539
So I am trying to apply [Text to Columns] and then MDY format, after that the date in a cell dissappears and the formula is displayed. My original date column A consists of the following:
– Nane Amiryan
Sep 19 '16 at 2:22
1. Dates in the format (9/20/2016)
– Nane Amiryan
Sep 19 '16 at 2:23
2. #N/A In order to not have #N/As in my dates column i add a second column B where I add a formula: =Iferror(cell from column A, "01/01/2018"
– Nane Amiryan
Sep 19 '16 at 2:25
Basically I am denoting every #N/A as 01/01/2018 in column. When I create the pivot table i drag column B for the date field
– Nane Amiryan
Sep 19 '16 at 2:26
Can it be that column B is a formula that's why it's not working? [Text to Column] when applying inside pivot table brings an error, when I apply in the be raw table before pivoting, it converts column B into it's formulas
– Nane Amiryan
Sep 19 '16 at 2:27
|
show 5 more comments
So I am trying to apply [Text to Columns] and then MDY format, after that the date in a cell dissappears and the formula is displayed. My original date column A consists of the following:
– Nane Amiryan
Sep 19 '16 at 2:22
1. Dates in the format (9/20/2016)
– Nane Amiryan
Sep 19 '16 at 2:23
2. #N/A In order to not have #N/As in my dates column i add a second column B where I add a formula: =Iferror(cell from column A, "01/01/2018"
– Nane Amiryan
Sep 19 '16 at 2:25
Basically I am denoting every #N/A as 01/01/2018 in column. When I create the pivot table i drag column B for the date field
– Nane Amiryan
Sep 19 '16 at 2:26
Can it be that column B is a formula that's why it's not working? [Text to Column] when applying inside pivot table brings an error, when I apply in the be raw table before pivoting, it converts column B into it's formulas
– Nane Amiryan
Sep 19 '16 at 2:27
So I am trying to apply [Text to Columns] and then MDY format, after that the date in a cell dissappears and the formula is displayed. My original date column A consists of the following:
– Nane Amiryan
Sep 19 '16 at 2:22
So I am trying to apply [Text to Columns] and then MDY format, after that the date in a cell dissappears and the formula is displayed. My original date column A consists of the following:
– Nane Amiryan
Sep 19 '16 at 2:22
1. Dates in the format (9/20/2016)
– Nane Amiryan
Sep 19 '16 at 2:23
1. Dates in the format (9/20/2016)
– Nane Amiryan
Sep 19 '16 at 2:23
2. #N/A In order to not have #N/As in my dates column i add a second column B where I add a formula: =Iferror(cell from column A, "01/01/2018"
– Nane Amiryan
Sep 19 '16 at 2:25
2. #N/A In order to not have #N/As in my dates column i add a second column B where I add a formula: =Iferror(cell from column A, "01/01/2018"
– Nane Amiryan
Sep 19 '16 at 2:25
Basically I am denoting every #N/A as 01/01/2018 in column. When I create the pivot table i drag column B for the date field
– Nane Amiryan
Sep 19 '16 at 2:26
Basically I am denoting every #N/A as 01/01/2018 in column. When I create the pivot table i drag column B for the date field
– Nane Amiryan
Sep 19 '16 at 2:26
Can it be that column B is a formula that's why it's not working? [Text to Column] when applying inside pivot table brings an error, when I apply in the be raw table before pivoting, it converts column B into it's formulas
– Nane Amiryan
Sep 19 '16 at 2:27
Can it be that column B is a formula that's why it's not working? [Text to Column] when applying inside pivot table brings an error, when I apply in the be raw table before pivoting, it converts column B into it's formulas
– Nane Amiryan
Sep 19 '16 at 2:27
|
show 5 more comments
Your PivotTable is interpreting "01/01/2018" as text rather than as a date.
Instead ofIFERROR(COLUMN B,"01/01/2018"), useIFERROR(COLUMN B,DATE(2018,1,1)).
answered Aug 18 '17 at 17:07
Dan Henderson
6521718
add a comment |
Your PivotTable is interpreting "01/01/2018" as text rather than as a date.
Instead ofIFERROR(COLUMN B,"01/01/2018"), useIFERROR(COLUMN B,DATE(2018,1,1)).
answered Aug 18 '17 at 17:07
Dan Henderson
6521718
add a comment |
Your PivotTable is interpreting "01/01/2018" as text rather than as a date.
Instead ofIFERROR(COLUMN B,"01/01/2018"), useIFERROR(COLUMN B,DATE(2018,1,1)).
answered Aug 18 '17 at 17:07
Dan Henderson
6521718
Your PivotTable is interpreting "01/01/2018" as text rather than as a date.
Instead ofIFERROR(COLUMN B,"01/01/2018"), useIFERROR(COLUMN B,DATE(2018,1,1)).
answered Aug 18 '17 at 17:07
Dan Henderson
6521718
answered Aug 18 '17 at 17:07
Dan Henderson
6521718
answered Aug 18 '17 at 17:07
Dan Henderson
6521718
answered Aug 18 '17 at 17:07
Dan Henderson
6521718
6521718
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1125762%2fexcel-pivot-table-does-not-group-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Try turning the column to "General". If the dates change to number then you should be able to group. If they remain dates then they are text strings that look like dates and not true dates in Excel.
– Scott Craner
Sep 19 '16 at 0:49
Thanks! I tried converting them into general - most of them got converted to numbers, but 5 of them remained as dates. ANother issue I tried grouping the ones that were converted to numbers and instead of bringing a hierarchy of month and year it just gives me Group one with the same filed title. Do you know why? The group selection field is greyed out on the home tab
– Nane Amiryan
Sep 19 '16 at 2:34