Grouping dates - Formatting Issue?

Hi,

I'm trying to create a pivot table on the data in the attached file and grouping the expenses into months by right clicking on the data column and selecting "Group". EXCEL gives me an error message "Cannot group that selection". I'm pretty sure it's something to do with the format of the date column (if I take a sample and manually change the dates to standard xx/xx/xxxx format it works fine). I've included the basic download (from a mobile phone) and the pivot table. If anyone can explain to me what the issue is and the solution I would be very grateful,

regards, Neil

thanks for reply,- I received the csv file via mail from the mobile phone, opened it in EXCEL and then proceeded to create the pivot table. What is the process to turn text dates into "real" dates... I did mess around with formulae (Left and Mid string) to try and re-create them in the format xx/xx/xxxx but this still didn't work.... thanks, Neil

Neil

I've been trying to find a way to convert the text dates but so far without 100% success.

I first tried Data>Text to columns but that didn't work.

I then tried formulas and had some luck.

The problem with using a formula is that , naturally, some dates have a single digit for the day and, unnaturally, some of the times only have 3 figures, eg 10:9 in A32.

So it's hard to come up with a formula which covers all of the values.

Hi Norie, thanks for your help so far.... I started doing an If statement using LEFT and MID etc.. but ended up with a long If statement trying to convert the text to the xx/xx/xxxx format. I assumed that there must be a simply way which is why I posted the question.

If that is the way to do it, I can try and write it using vba. I'm suprised as I thought that this must be a common issue!

regards,

Neil

Neil

It is a common issue, but these 'dates' aren't even uniformly wrong.

By the way, I don't see how VBA will help though I suppose you could take advantage of structures like Select Case.

I finally came up with a formula that appears to work.

Formula:

Please Login or Register  to view this content.

Hi Norie, that seems to do the job, thankyou very much. I was thinking of vba simply because I'm more familiar with it than using formula in the worksheet. One of the issues that I came across using the Mid function was that for days the figure could be 1 or 2 digits ( e.g. 9 or 10) - I thought I could handle that better with code. I had my mind on Select Case to convert the months to figures e.g. Jan to 1. Once again thankyou for your support ... regards, Neil

Neil

You would probably end up using basically the same functions in VBA eg Left, Right etc.

I'm definitely more comfortable with VBA myself and I've just found out that VBA's DateValue
will convert to dates without any string manipulation.

So this code would do it.

Please Login or Register  to view this content.

Can't believe I didn't try that before all the worksheet formula stuff.

I just assumed because the worksheet function didn't work the VBA equivalent wouldn't either.

Hi Norie, getting better all the time, thanks, again! best wishes, Neil