Menu
I have been given a csv with a column called month as a char variable with the first three letters of the month. E.g.:
"Jan", "Feb","Mar",..."Dec" Is there any way to convert this to a numeric representation of the month, 1 to 12, or even a type that is in a date format?
751
An alternative way to get a month number from an Excel date is using the TEXT function: =TEXT(A2, "m") - returns a month number without a leading zero, as 1 - 12. =TEXT(A2,"mm") - returns a month number with a leading zero, as 01 - 12.
Select a blank cell next to the sales table, type the formula =TEXT(A2*29,"mmm") (Note: A2 is the first number of the Month list you will convert to month name), and then drag the AutoFill Handle down to other cells. Now you will see the numbers (from 1 to 12) are converted to normal month names.
For this, simply pick Date in the Number Format box on the Home tab. To apply a format other than default, then select the cells with serial numbers and press Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK. Yep, it's that easy!
Use match and the predefined vector month.abb:
tst <- c("Jan","Mar","Dec") match(tst,month.abb) [1] 1 3 12 You can use the built-in vector month.abb to check against when converting to a number, eg :
mm <- c("Jan","Dec","jan","Mar","Apr") sapply(mm,function(x) grep(paste("(?i)",x,sep=""),month.abb)) Jan Dec jan Mar Apr 1 12 1 3 4 The grep construct takes care of differences in capitalization. If that's not needed,
match(mm,month.abb) works just as fine.
If you also have a day and a year column, you can use any of the conversion functions, using the appropriate codes (see also ?strftime)
eg
mm <- c("Jan","Dec","jan","Mar","Apr") year <- c(1998,1998,1999,1999,1999) day <- c(4,10,3,16,25) dates <- paste(year,mm,day,sep="-") strptime(dates,format="%Y-%b-%d") [1] "1998-01-04" "1998-12-10" "1999-01-03" "1999-03-16" "1999-04-25" If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
