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
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
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
: 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
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 )