Number to date problem Excel to R

General
1

Hi to everyone,

I read an excel file

> library(openxlsx)
bd <- read.xlsx("BASDAT.xlsm")

And I get number dates

image
This date represent 01/07/2017. July 01, 2017.

Is there a way to convert this to date format? I know excel dates starts at 01/01/1900 So I tryed:

> library(lubridate)
> dates <-42917:42918
> as.Date('1900-01-01') + days(dates)
[1] "2017-07-03" "2017-07-04"

But, 42917 represents July 03, 2017 instead of Excel's July 01, 2017. Do you have any idea to solve this problem? I could try with

> library(lubridate)
dates <-42917:42918
as.Date('1899-12-30') + days(dates)
[1] "2017-07-01" "2017-07-02"

Really I don't think doing this It's the best way to do this. So If you have any solution I would be very grateful.

2

Just use the origin argument for as.Date()

dates <- 42917:42918
as.Date(dates, origin = "1899-12-30")
#> [1] "2017-07-01" "2017-07-02"

Created on 2019-09-17 by the reprex package (v0.3.0.9000)

3

The read.xlsx() function also has the parameter detectDates which defaults to FALSE. So, you might just try

library(openxlsx)
bd <- read.xlsx("BASDAT.xlsm", detectDates = TRUE)
2 Likes
4

A small trivia piece: all Excel dates start at New Year's Eve 1899, not January 1st 1900. Excel considers the year 1900 as a leap year, which it was not.

This is a bug introduced by Microsoft on purpose for reasons of compatibility with Lotus 1-2-3, a popular pre-windows spreadsheet tool.

Be mindful of this when adding serial dates originating from Excel to a start date in a different system (such as R).

3 Likes
5

Thanks for this info.

I solved my problem with

Data$FECHA <- convertToDate(Data$FECHA)

Thanks to all!

6

It gets worse! Even more trivia (in case you ever have to process workbooks that were created or edited in Excel for Mac):

By default, Microsoft Excel for the Macintosh uses the 1904 date system. Because of the design of early Macintosh computers, dates before January 1, 1904 were not supported. This design was intended to prevent problems related to the fact that 1900 was not a leap year. If you switch to the 1900 date system, Excel for the Macintosh does support dates as early as January 1, 1900.

7

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.