Excel dates formatted in "YYYY MMM DD 00:00:00.000" need the dates to be in DD-MMM-YYYY

Question

Is there a way to program a macro to convert all dates in a worksheet to go from "2009 APR 01 00:00:00.000" to a date format "dd-mmm-yyyy"? I have a data set that is regularly pulled and all the dates in 40+ columns are basically useless in 2009 APR 01 00:00:00.000, any ideas on how to create this type of macro and be able to share with others using the same data?

Thanks!

Answer 1

Assuming Excel recognizes them as dates, you can just change the cell formatting to the proper date format. Highlight column, right-click -> format cells...

Answer 2

I don't have the full answer for you, but here's what I would do roughly:

1. Get the range of the entire spreadsheet so you know what you have to iterate through.
2. Iterate through each cell.
3. When on each cell, check if it is a date format already.
4. If cell format is date format already, then use the following code: Selection.NumberFormat = "dd-mmm-yyyy".
5. If not, then move on the the next cell.

Good luck!

Answer 3

It looks like your dates are always the same length of string. Here's one possible solution:

orgDate = "2009 APR 01 00:00:00.000"

newDate = mid(orgDate,10,2) & "-" & mid(orgDate,6,3) & "-" & left(orgDate,4)

Iterate through each cell with a loop and perform the above string operation, then put newDate back in the cell.