How can I retrive the time only with xlconnect?

问题:

I have data saved in .xlsx file, and in the data the columns date and time separated. However, I type the code;

data1<-readWorksheetFromFile("file.xlsx", sheet="Sheet1")

to retrive data into R. As a consequence, I am getting the time column in the R environment, with nonsense date attached to it. So the time column in R looks like 1899-12-31 16:07:11, what I want only the take the time as 16:07:11 in this case. How can that be done ?

回答1:

XLConnect uses POSIXct to represent date-time values in a data.frame. If you only want to extract the time component you may use strftime(your_column, format = "%H:%M:%S") (representation as character).

Some background as to why you get 1899-12-31: Excel stores date-time values as decimal numbers representing the number of days since 1900-01-00 (yes, day 00! - which doesn't exist). Fractional days represent hours, minutes and seconds. As such the decimal number 1.0 represents 1900-01-01 and the number 0.0 represents 1900-01-00. Since the latter doesn't exist, in XLConnect it's actually represented as the day before day 1 which is 1899-12-31. Time-only values are represented as decimal numbers 0.xxx in Excel. In accordance with the above, when imported in XLConnect this results in 1899-12-31 HH:MM:SS.

Additional information on Excel date-time values can be found here.