Dates imported as text; year/month/day

[MVF]

I receive spreadsheets from South Africa, and they populate the date fields as text, for example, 20110530. Can I convert that to a US format 05/30/2011 without using left/mid/right functions to extract the number and concatenating it all back together? Other than doing that, I get ###### in the cell that it is too long to display. Thanks!!!

 

[Jonmo1]

Welcome to the board...

Try
Data - Text to columns
Deliminated - Next
Next
Date - YMD
Finish


Hope that helps.

 

[MVF]

Wow, that was fast. Thanks, looks great!!

I then tried to use the "group field" function in my pivot table on that data that I changed so I wouldn't see dates by day, but by month.

However, the button is grayed out. When I pull in other dates from the same workbook, in the same format, the group field button is active.

Is there something about pasted data that doesn't allow this to work?

Thanks again

 

[Jonmo1]

Perhaps you need to re-create the pivot table after converting the dates with text to columns..?

 

[MVF]

Gave it a try, no luck. when I manually type the data into a fresh worksheet, using the text to column approach that you described, grouping works fine.

I'll wait for my next spreadsheet download and recreate it. Perhaps that will do the trick.

Thanks again for your feedback!!