Rounding a date to month and year

[nroy]

Hi,

I'm trying to group some dates by month in a pivot table but I'm getting a message saying 'cannot group that selection'.

I'm trying to do it another way. I want to add another column to my data which will convert the full date to month and year. I've tried =TEXT(H2,"mmm-yyyy") which works but as it's text I can't sort in month order.

Does anyone know a formula that will remove the day but still recognise it as a date and allow me to sort the data in decending order?

Thanks
Nicola

 

[JamesW]

=TEXT(H2,"mmm-yyyy") works fine for me.

What is in H2 exactly? Is this stored as a date or text?

 

[jasonb75]

=TEXT(H2,"mmm-yyyy")

is exactly what it says it is, TEXT, dates are numbers, so it means that you no longer have a valid date, and trying to sort them would set the order

Apr,Aug,Dec,Feb,Jan,Jul,Jun,Mar,May,Nov,Oct,Sep

however, =--TEXT(H2,"mmm-yyyy")

would default the dates to the 1st of the month, which is a valid date, allowing you to sort correctly.

 

[nroy]

Thanks so much. I used =--TEXT(H2,"mmm-yyyy") and it works perfectly!

 

[JamesW]

=TEXT(H2,"mmm-yyyy")

is exactly what it says it is, TEXT, dates are numbers, so it means that you no longer have a valid date, and trying to sort them would set the order

Apr,Aug,Dec,Feb,Jan,Jul,Jun,Mar,May,Nov,Oct,Sep

however, =--TEXT(H2,"mmm-yyyy")

would default the dates to the 1st of the month, which is a valid date, allowing you to sort correctly.

That's what I thought, but I just used =TEXT(H2,"mmmm-yyyy") and it sorted it Jan - Dec...

 

[jasonb75]

That's what I thought, but I just used =TEXT(H2,"mmmm-yyyy") and it sorted it Jan - Dec...

Looking into it a little more, it appears it would depend how you sort (version of excel, or option settings may also have some effect).

For me (using excel 07), the standard sort was listing months alphabetically, but advanced sort, treat text that looks like a number as a number, sorted the months chronologically.

 

[JamesW]

That's probably it. Good call on the double unary though!

 

[Peter_SSs]

however, =--TEXT(H2,"mmm-yyyy")

would default the dates to the 1st of the month, which is a valid date, allowing you to sort correctly.

Good call on the double unary though!

Could also just sick with numerical values:
=H2-DAY(H2)+1