Reading in dates from Excel into R -

i have multiple csv files need read r. first column of files contain dates , times, converting posixlt when have loaded data frame. each of csv files have dates , times formatted in same way in excel, however, files read in differently.

for example,

my file looks once imported:

  date                value 1 2011/01/01 00:00:00 39 2 2011/01/01 00:15:00 35 3 2011/01/01 00:30:00 38 4 2011/01/01 00:45:00 39 5 2011/01/01 01:00:00 38 6 2011/01/01 01:15:00 38 

therefore, code use amend format is:

data$date <- as.posixlt(data$date,format="%y/%m/%d %h:%m:%s") 

however, files being read in as:

  date             value 1 01/01/2011 00:00 39 2 01/01/2011 00:15 35 3 01/01/2011 00:30 38 4 01/01/2011 00:45 39 5 01/01/2011 01:00 38 6 01/01/2011 01:15 38 

which means format section of code not work , gives error. therefore, there anyway automatically detect format date column in? or, there way of knowing how read, since format of column in excel same on both.

when using wrong formatting string date input, seem na values. if case, solve problem in 2 steps. first, format dates excel assuming have 3 of hours, minutes, , seconds:

date.original <- data$date data$date <- as.posixlt(data$date,format="%y/%m/%d %h:%m:%s") 

this should leave na values in date column dates missing seconds. can try this:

data$date[is.na(data$date)] <- as.posixlt(date.original, format="%y/%m/%d %h:%m") 

this should cover remaining data.

data

data <- data.frame(date=c('2011/01/01 00:00:00', '2011/01/01 00:15',                           '2011/01/01 00:30:00', '2011/01/01 00:45'),                    value=c(39, 35, 38, 39))