-2

I import dates from Excel into R.
Some of the values appear as real numbers:
e.g. 43313.458055555559
I would like to convert these numbers into a
timestamp representation like this - 9/4/2018 11:45:35AM

Thanks in advance.

nghauran
  • 6,648
  • 2
  • 20
  • 29
ronencozen
  • 1,991
  • 1
  • 15
  • 26
  • 1
    See the Other Applications section of the Help Desk article in R News 4/1 https://www.r-project.org/doc/Rnews/Rnews_2004-1.pdf – G. Grothendieck Aug 27 '18 at 13:43
  • Duplicate as search shows options using date time packages: https://stackoverflow.com/questions/47093228/convert-excel-numeric-to-and https://stackoverflow.com/questions/43230470/how-to-convert-excel-date-format-to-proper-date-with-lubridate – Adam Sampson Aug 27 '18 at 14:12
  • 1
    Based on G. Grothendieck's comment, try: `as.POSIXct(as.Date(43313.458055555559, origin = "1899-12-30"))` – nghauran Aug 27 '18 at 14:12
  • 1
    @ANG could you convert your comment into an answer. – hannes101 Aug 27 '18 at 14:24
  • 2
    Possible duplicate of [Convert Excel numeric to date](https://stackoverflow.com/questions/47093228/convert-excel-numeric-to-date) – hannes101 Aug 27 '18 at 14:25

1 Answers1

1

Based on G. Grothendieck's comment, try:

as.POSIXct(as.Date(43313.458055555559, origin = "1899-12-30"))

Just replace 43313.458055555559 by DataFrame.name$Column.name.

Spreadsheets like Microsoft Excel on a Windows PC or OpenOffice.org represent datetimes as days and fraction of days since December 30, 1899 (usually). If x is a vector of such numbers then as.Date("1899-12-30") + floor(x) will give a vector of Date class dates with respect to Date’s origin. Similarly chron("12/30/1899") + x will give chron dates relative to chron’s origin. Excel on a Mac usually represents dates as days and fraction of days since January 1, 1904 so as.Date("1904-01-01") + floor(x) and chron("01/01/1904") + x convert vectors of numbers representing such dates to Date and chron respectively. Its possible to set Excel to use either origin which is why the word usually was employed above.

Source: https://www.r-project.org/doc/Rnews/Rnews_2004-1.pdf

nghauran
  • 6,648
  • 2
  • 20
  • 29