Converting Numerals to Date

**frutz** · 07-15-2009, 05:28 AM

Hi All,

I have a column of numbers (5 or 6 digits) which represent month and year

E.g. 20091 = January 2009, 201112 = December 2011, and so on.

Is there any way I can convert this back into MMYYYY form?

Alternatively, I have this problem because I needed to create a pivot table in ascending order going by months of the year which required 'grouping' the various dates (DD-MM-YYYY). The only way I found I could do this was to use =VALUE(CONCATENATE(YEAR(F2),MONTH(F2))), which gave me this problem of 20091, etc in the first place.

Any one have suggestions?

**DonkeyOte** · 07-15-2009, 05:34 AM

You should be able to Group the PT based on dates in F there is seemingly no need for creation of any additional columns... ie you can group the Date field (F) in your PT by Month & Year - right click on any given Date value and proceed to Group.

see: http://www.contextures.com/xlPivot07.html

**frutz** · 07-15-2009, 06:07 AM

Great recommendation, I took your advice and it works, thanks!

Could I just find out though, for the data being pivoted, is there any way I could leave the date open-ended? I have data that runs up to row 82 currently, but as data is added on a daily basis (row 83 onward), is there any way I could use the same pivot table ad infinitum? So far I've added dummy date values so that I can use "Group" but is there another way to do this?

**frutz** · 07-15-2009, 06:14 AM

Oh no, I'm sorry but on closer look Group does not work for me because I need to have my data in the following order:

Aug 09
Sept 09...
Jan 10 etc

That is one of the reasons i had to concatenate the date at first, so that i could later on group the data by months. any way around this?

**DonkeyOte** · 07-15-2009, 06:19 AM

I don't understand your point I'm afraid, Grouping should still allow you to sort your dates... perhaps you'd be better off posting a small sample outlining desired results.

**frutz** · 07-15-2009, 06:28 AM

Hi DonkeyOte,

It's alright, managed to get the result i want with dummy data so it's ok.

What I meant was grouping causes ALL january, feb etc to be grouped, but I need them to be seperated in ascending order: Nov '10, Dec '10, Jan '11...Jan '12 (instead of months of all years being grouped).

But thanks for the advice and reference to that link!