CodeDairy.com

by A.R. Vital

   Usually in most of the reports when downloaded, date format will be in “dd.mm.yyyy” format.
But Excel will not read this date and shows as a text.
Now we will see how we can change it in to the date format which Excel can read using the Text Functions

For Example:

  Cell A2 contains the following date 21.11.2011, Surely excel will not read it as a date.
So in B5 we will type the following formula

 = MID ( A2, 4, 2 ) & "/" & LEFT ( A2, 2 ) & "/" & RIGHT ( A2, 4 )
 

Result will be in the format 11/21/2011 means 11th Month, 21st Date and 2011 Year

Now we will look detailed on the three functions used. These are

MID :

   It will extract the number of characters which we specify from a position which we give

Syntax: = MID ( text, start_num, num_chars )
	Text: is the cell reference or the text or a string
	Start_num: is the position from where excel has to consider
        Num_ chars: is the number which you want to extract

   In the above example, we told excel to extract 2 chars from the 4th position,
so in “21.11.2011″ 4th position is 1 from their 2 characters that is 11, which is the month

LEFT

   : As the function name indicates, it will extract the number of characters from left of
the reference given

  Syntax: = LEFT ( text, [ num_chars ] )
            Text: is the cell reference or the text
            Num_chars: is the number which you want to extract

   In the above example we asked excel to extract the first two characters from Left

LEFT ( A2 , 2 ) “.

So from the given reference (21.11.2011) it will extract first two characters i.e “21″ the date

Next is Right Function

Right:

It extracts the specified number of characters from right side of reference given.

    Syntax: = RIGHT ( text, [ num_chars ] )
              Text: is the cell reference or the text
              Num_chars: is the number which you want to extract

   In the given example, Excel will extract the four characters from Right side of the reference “21.11.2011″ that is “2011″ which is the year

   As last we have asked excel to concatenate, to concatenate I have used & instead of Concatenate formula. I.e. we can use & instead of Concatenate

So the final formula is

 = MID ( A2, 4, 2) & "/" & LEFT ( A2, 2 ) & "/" & RIGHT ( A2, 4 )

And the result is 11/21/2011

   That’s it. If you want to use this function regularly then my suggestion it to save the formula in
the Auto Correct Spelling. As I will use this regularly I have saved the below formula in the Auto Correct
Spelling options with the word “d…” Just insert a column after the date column which you want to convert and just type
“d…” that’s it formula will automatically take the data from the cell before and changes it into “mm/dd/yyyy” format.

Below is my formula

   = MID ( INDIRECT ( ADDRESS ( ROW(), COLUMN() - 1 ) ), 4, 2 ) & "/" & 
LEFT ( INDIRECT ( ADDRESS ( ROW (), COLUMN() - 1 ) ), 2 )
     & "/" & RIGHT ( INDIRECT ( ADDRESS ( ROW(), COLUMN() - 1 ) ), 4 )

Thanks for reading my post. please do not hesitate to post your comments or ask any questions below ( or even you find any mistakes too )