Changing Dates to Months

[Redgirl9487]

Hi I was wondering if anyone could help me please? I am trying to group the attached from dates into months?
Many thanks

 

[Joe4]

If you have a valid date in cell A1, you can get the month of it like this:

=TEXT(A1,"mmmm")

If the dates span multiple years where you need the year portion for your grouping, you can include year like this:

=TEXT(A1,"mmmmyyyy")

 

[Redgirl9487]

Sorry I should have mentioned this is already in a pivot table.

 

[Joe4]

Take a look here then: Using the Excel Pivot Table Grouping by Month to View Data Differently - Pryor Learning.

 

[Redgirl9487]

Thanks. I have been selecting group but it just comes up with an error message "cannot group that selection".

 

[Joe4]

Are your dates really entered as dates, or are they entered as text?

 

[Redgirl9487]

Sorry I might sound stupid but what do you mean?

 

[Joe4]

In order to group by date, they have to be valid date entries. You could have a valid date entry, or what looks like a date entry, but is really text (and with text, you could enter anything).
Date functions/operations only work on valid date entries.

Dates in Excel are really stored as numbers, specifically the numbers of days since 1/0/1900. So they are really just numbers with special date formatting applied to them.
An easy way to check to see if you have a valid date entry or a text entry is to apply the ISNUMBER function on it.

Let's say that your entry is in cell A2. Then in any blank cell, enter this formula and see what it returns:

=ISNUMBER(A2)

If it returns "TRUE", you have a valid date entry in cell A2. If it returns "FALSE", you have a text entry in cell A2, not a valid date entry.

You would then need to convert all your values in that column from Text to Dates. You can do that by using "Text to Columns" on that column, choosing the Date option with the proper date structure (appears to be "DMY" in your example).