Cell date format refresh

[Jessica.Bush]

Hi all,

I wonder if someone could help please?

In the attached I have a list of dates. The dates are the first date of each month but are not formatted correctly.
If I am doing this manually then I would change the format of my cells to date. This wouldn't be enough though and it would only change if I click in and out of the cell. As I can have thousands of rows I would normally use text to columns to refresh all cells at once and get my data in the correct cell format of a date.

How can I do the same in code?

When I try to replicate this in code using the attached data, it works but my dates end up wrong. Instead of reading the first date of every month it switches the date and month round so that all dates are in January.

Any help is appreciated. File attached.

[Ron Coderre]

Using your posted workbook...
Try this

• Select the single-column range of "dates"
• Data.Text-to-columns...Click: Finish
That will convert the text to actual dates.


EDITED TO INCLUDE THIS COMMENT:
Since I'm on the other side of the pond...I needed to indicate to Excel that text-dates are d/m/y
• Select the single-column range of "dates"
• Data.Text-to-columns...Click: Next...Click: Next
...Check: Date....DMY...Click: Finish



Is that something you can work with?

[Jessica.Bush]

Thanks for the help Ron, however, if the dates are in the correct format and the format has been applied (cells clicked into) already, then running the macro on this will change the dates switching the month and days. If the format has not been applied, then this does work.

I have attached an example with the macro in the workbook where the first two columns have not got the formats applied to them but the third one has.

I need it to run automatically on all columns, therefore is there a way I can differentiate between the columns where the formats have been applied and haven’t so that the dates don’t change on the columns that are already correct?

File is attached again. Thanks in advance. Dates Example.xlsm

[Jessica.Bush]

Just to say that I found a work around to this problem. On 3 unused columns to the right hand side I used a formula to show date, the next column for month and the next one year and copied and pasted as values.
I was then able to put them back together again to get the date in the format required.

Thanks for the help.