Search code examples
rexcelcharacterdate-conversion

Convert character YYYY-MM-00 into date YYYY-MM in R

I imported Excel data into R and I have a problem to convert dates. In R, my data are character and look like :

date<-c('1971-02-00 00:00:00', '1979-06-00 00:00:00')

I would like to convert character into date (MM/YYYY) but the '00' value used for days poses a problem and 'NA' are returned systematically. It works when I manually replace '00' with '01' and then use as.yearmon, ymd and format. But I have lots of dates to change and I don't know how to change all my '00' into '01' in R. 


# data exemple
date1<-c('1971-02-00 00:00:00', '1979-06-00 00:00:00')

# removing time -> doesn't work because of the '00' day
date1c<-format(strptime(date1, format = "%Y-%m-%d"), "%Y/%m/%d")
date1c<-format(strptime(date1, format = '%Y-%m'), '%Y/%m')

# trying to convert character into date -> doesn't work either
date1c<-ymd(date1)
date1c<-strptime(date1, format = "%Y-%m-%d %H:%M:%S")
date1c<-as.Date(date1, format="%Y-%m-%d %H:%M:%S")
date1c<as.yearmon(date1, format='%Y%m')

# everything works if days are '01'
date2<-c('1971-02-01 00:00:00', '1979-06-01 00:00:00')
date2c<-as.yearmon(ymd(format(strptime(date2, format = "%Y-%m-%d"), "%Y/%m/%d")))
date2c

If you have an idea to do it or an another idea to solve my problem, I would be thankful!

Solution

  • Another possibility could be:

    as.Date(paste0(substr(date1, 1, 9), "1"), format = "%Y-%m-%d")

[1] "1971-02-01" "1979-06-01"

    Here it extracts the first nine characters, pastes it together with 1 and then converts it into a date object.