CODEWITHSUNDEEP
rread.csv
hema parthasarathy
hema parthasarathy

Reputation: 19

Reading date time values from excel in R

I have a column in following format,

5/26/2015 11:45
5/26/2015 12:00
5/26/2015 12:15
5/26/2015 12:30
5/26/2015 12:45

When I read using read.csv in R, it reads as follows,

42155.76
42155.77
42155.78
42155.79
42155.8

What is the problem? How to read in same format?

Upvotes: 1

Views: 1086

Answers (1)

Pierre L
Pierre L

Reputation: 28461

as.POSIXct(x*86400, origin="1900-01-01")
[1] "2015-06-02 14:14:24 EDT" "2015-06-02 14:28:47 EDT"
[3] "2015-06-02 14:43:12 EDT" "2015-06-02 14:57:36 EDT"
[5] "2015-06-02 15:12:00 EDT"

When you view the file in Excel or similar program it shows you the arranged and formatted date, but saves it internally as days since January 1, 1900. That is what is getting saved in the csv file.

Also note that the origin date may be different than the standard. From R documentation on ?as.Date

Most systems record dates internally as the number of days since some origin, but this is fraught with problems,...The only safe procedure is to check the other systems values for known dates: reports on the Internet (including R-help) are more often wrong than right.

And,

Excel is said to use 1900-01-01 as day 1 (Windows default) or 1904-01-01 as day 0 (Mac default), but this is complicated by Excel incorrectly treating 1900 as a leap year. So for dates (post-1901) from Windows Excel as.Date(35981, origin = "1899-12-30") # 1998-07-05

Data

x <- c(42155.76,42155.77,42155.78,42155.79,42155.8)

Upvotes: 3

Related Questions