How do I keep the date-format from Excel when importing a time series in R?-CodePudding

When I import a financial time series from Excel to R, the dates turn into integers. Each column represents a date. I understand that Excel converted them to a form where they represent the number of days since the origin date of 01/01/1900. How can I turn these numbers back into 'normal' dates in R?

CodePudding user response：

I would use one of these functions:

xlsnum_to_POSIXct <- function(n)
{
    as.POSIXct(
       x = n*24*60*60,
       origin = "1900-01-01 00:00:00"
    );
}

xlsnum_to_POSIXlt <- function(n)
{
    as.POSIXlt(
       x = n*24*60*60,
       origin = "1900-01-01 00:00:00"
    );
}

which converts the number of days into number of seconds since 1900/01/01, and wraps it conveniently into an R date-time type.

Should work also with fractions of day, vectors of day counts etc.

CodePudding user response：

You can change data type from integer to date using function as.Date and setting a date origin.

If your dataset is called df and your column with the dates is called event_date, for example:

df$event_date <- as.Date(df$event_date, origin = "1899-12-30")

Or, using mutate from package dplyr:

df <- mutate(df, event_date = as.Date(event_date, origin = "1899-12-30"))