0

I am reading in some numbers from excel that are the numeric version of a date. Excel displays 42094 as 3/31/15 (or some variant depending how the Date is structured. as.Date(42094,origin = "1899-12-30") produces "2015-03-31". The choice of origin has to do with Microsoft misunderstanding about leap years. These two lines of code

    temp <- as.Date(42094,origin = "1899-12-30")
    as.POSIXct(temp, origin = "1899-12-30")

produce

"1899-12-30 04:41:34 MST"

With as.POSIXct(temp, origin = "1900-1-1") the result is "2015-03-30 18:00:00 MDT"

Now what I want is

"2015-03-31 MDT"

or something similar

Gerald Nelson
  • 43
  • 4
  • The date (and time) data come to me in an excel spreadsheet; i want to convert them to POSIXct date time. Col 1 is the date the data were collected. Col 2 is a counter for the number of minutes since midnight. The first column has a date format. The first value is 3/31/2015. This is the correct date for the data, which were collected in the America/Denver timezone. When I change the format to numeric, the value is 42094. This is the number of days since Jan 1, 1990. – Gerald Nelson Apr 22 '15 at 14:17

1 Answers1

0

Part of the solution to my problem was this line of code. origin=as.POSIXct("1970-01-01", tz="America/Denver") With the usual way of using origin (origin = "1970-01-01"), GMT is assumed; this code changes that to a local time zone.

The code from #pnuts converts days to 2015-3-31 from the microsoft 1900 origin (42094) to the R origin of 1970-1-1 (25569). It then converts days to seconds (86400) and then adds my hours B1 converted to seconds. So here's a couple of lines of code that do what I want for the date part of my data.

    temp <- (42094-25569)*86400
    as.POSIXct(temp, origin=as.POSIXct("1970-01-01", tz="America/Denver"))

The result is 

    "2015-03-31 01:00:00 MDT"
Gerald Nelson
  • 43
  • 4