Now a xlsx file contained a date column as :
Date 2019-3-1 0:15 2019-3-1 19:15 2019-3-1 23:15 How can I load it into data.frame as read date and time datatype? My tool is openxlsx package and I tried like:
df <- readWorkbook(xlsxFile = '0301-0314.xlsx',sheet=1) First you read the data set using any library. Then you can try as.POSIXlt or as.POSIXct to define the date-time format. This also allows you to provide timezone info along with date-time format. Example:
> sampledf <- data.frame(DateTime = c("2019-3-1 0:15", + "2019-3-1 19:15", + "2019-3-1 23:15") + ) > str(sampledf$DateTime) Factor w/ 3 levels "2019-3-1 0:15",..: 1 2 3 > sampledf$DateTime <- as.POSIXlt(sampledf$DateTime ,"GMT",format = "%Y-%m-%d %H:%M") > str(sampledf$DateTime) POSIXlt[1:3], format: "2019-03-01 00:15:00" "2019-03-01 19:15:00" ... Timezone info "GMT" can be replace with any time zone. More about different time formatting options in R is available here.
This will work:
# Create example dataset: df <- data.frame(Date = c("2019-3-1 0:15", "2019-3-1 19:15", "2019-3-1 23:15") ) df$Date <- as.character(df$Date) # Format "Date" as date and time: df$time <- strptime(as.character(df$Date), "%Y-%m-%d %H:%M") # Check: str(df) # If then you would like to count time, for example in number of hours, from a certain initial time (e.g. 2019-3-1 0:15) try: df$timestep <- as.numeric(difftime(time2="2019-3-1 0:15", time1=df$time, units="hours")) 