My question sounds quite silly- I’m looking for a way to convert the date format from "mmm-dd-yy" to "dd-mmm-yy" efficiently. For example, given a date 05/13/2006, it's clearly in month/day/year format rather than day/month/year. The "format--> cells-->custom-->mmm-dd-yy" with Excel does not recognize it because it assumes it’s in day/month/year and can not find the 13th of month in a year; it can not convert it into May-13-2006. If one gets only a handful of dates which need to be converted, it can be done manually (simply just type in 13/05/2006). But if there’re over 1,000 of dates, it’s really time consuming. I tried to write a function/procure to achieve this “simple” goal without success.
Could you please help? Many thanks for your time.

Hi Cecelia

Not a silly question at all. Your challenge is to get a the month/day/year arrangement into day/month/year this can be done using the the text functions to Left, Mid and Replace along with & for concatenation or joining together.

First you need to format the column or rows that contain the dates to a day/month/year format. The custom format for this in the type box is dd mm yyyy.

Your cell values that you want to change must be in the mm/dd/yyyy meaning your formula bar should show the value in that format

This is very import because you willl next need to convert your date into that order ie. May 15 2006 is "05/15/2006" in old format however you need it the other way around "15/05/2006".

The folowing steps take place in a seperate cells of course it coud be done as a string in VBA

First step is to use the LEFT function to extract the month ie. "05"(check your function wizard for the arguements used in this function.
This function then needs to be nested in the REPLACE function to replace where the day "15" and the day text is located by using the MID function to place it at the start.

Now you can concatenate with the "&" to rest of the date order dd/mm/yyyy using MID to exact the rest as individual components in the right order

At this stage it is still a text string. To convert it into a value that excel can recongnise as a date use VALUE to encase the whole formula in.

The whole formula is below using b4 as the cell with incorrect date format

=VALUE((REPLACE((LEFT(B4,2)),1,2,MID(B4,4,2))&"/"&MID(B4,1,2)&"/"&MID(B4,7,6)))

I hope this has been of assistance

Regards


BEST Training

Many thanks, very clear and helpful indeed!