microsoft excel courses london - Converting Date Format

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