Formulas‎ > ‎Formulas‎ > ‎

Convert month in text to number

posted Jan 28, 2011, 11:48 PM by Ayush Jain   [ updated Jan 29, 2011, 9:34 PM ]
When we pull data from different sources i.e. SQL, txt file, CSV files...., The date columns are imported in text format because of their source data type or could be due to any other reason.

For example - You have data of month in text format with no fixed length(at least three characters) like Jan, Febr, March, Apr, May etc. & you want to convert them into number as 1,2,3,4,5.

In this case, the best formula you can use to convert month in text to number is
=Month(A1&1)
OR
=Month("Apr"&1)


You can also use this formula as sub function in Date function .
Example :
=DATE(2011, MONTH(A1&1),2)
The Result would be : 02/04/2011

Let us know if you know better solution !
Comments