Tuesday, July 24, 2012

Convert month from numeric to alphabet

Here is how to convert month from number to text. For example, from 07 to July..
  • We have to use date() function to help this conversion.
    • In cell A1, cell value = 7
    • In cell A2, type this function "=DATE(2012,A1,1)" -> this is for change text in cell A1 to date data type by adding day 1th and year 2012
  • For convertion, we will use text() function to change month to format which we want
    • In cell A3, type this function "=TEXT(A2, "mmm")" -> to change to Jul
    • Display in cell A3 will be "Jul"
    • If we need "JUL", type upper() function before text() in cell A3 -> "=UPPER(TEXT(A2, "mmm"))"
    • We can change format instead of "mmm" to any which we want such as
      • "mmm." -> this will show the abbrevation of month name with dot ex. "Jul."
      • "mmmm" -> this will show full format of much ex. "July"
      • "mmmmm" -> this will show the first alphabet of month ex. "J"