Date formatting

[Chris Slater]

Dear All
I am copying and pasting data from one sheet to another in the same workbook but try as I may it doesn't seem to format correctly.
What I mean by that is that when I then create a lookup, some rows are contained within year subgroups - as I want it - but some are not.
I have tried formatting the recipient cells with the date format I want and then pasting "values only" but it just doesn't seem to work.
If I go down each cell and open it via 'F2' it then fixes the problem but there are too many to make this a usable solution.

As you can see from the attached image, the 'Autofilter' results show some of the rows correctly in 'Year' and 'Month' sub-filters on the dropdown list but there are also many that, although they look like dates, are not treated as such. I need a quick way of formatting the entire column so that Excel treats them as true dates.
Any ideas please?

 

[Alex Blakenburg]

What is the date format at the source and what is the date format for the destination ?
It seems likely from your screenshot that the source format was dd/mm/yyyy and the destinaions default format is mm/dd/yyyy.

If that is the case you should be able to use Text to Columns to convert the dates.

 

[Chris Slater]

What is the date format at the source and what is the date format for the destination ?
It seems likely from your screenshot that the source format was dd/mm/yyyy and the destinaions default format is mm/dd/yyyy.

If that is the case you should be able to use Text to Columns to convert the dates.

View attachment 56917

Hi Alex, Let me have a play around with that and come back to you if I may.
Talk soon

 

[Chris Slater]

Hi Alex, Let me have a play around with that and come back to you if I may.
Talk soon

Brilliant Alex,
That's done the trick.
Thanks that's saved me hours.
Is there a "Star" grading system that I can thanks you on?

 

[Alex Blakenburg]

I appreciate the offer. The closest the forum has to a rating system is the number of likes and marking the post that worked for you as a solution.
Glad I could help.