Converting Excel Datetime Serial Number to R Datetime

Converting excel DateTime serial number to R DateTime

Your number is counting days. Convert to seconds, and you're all set (less a rounding error)

helpData[["ExcelDate"]] <- 
  as.POSIXct(helpData[["ExcelNum"]] * (60*60*24)
    , origin="1899-12-30"
    , tz="GMT")


#     ID        DateTime ExcelNum           ExcelDate
#  1   1   3/4/2011 6:00 40606.25 2011-03-04 06:00:00
#  2   2  3/11/2011 7:55 40613.33 2011-03-11 07:54:59
#  3   3  3/13/2011 7:55 40615.33 2011-03-13 07:54:59
#  4   4  3/14/2011 0:00 40616.00 2011-03-14 00:00:00
#  5   5 3/14/2011 10:04 40616.42 2011-03-14 10:03:59
#  6   6  3/14/2011 7:55 40616.33 2011-03-14 07:54:59
#  7   7 3/15/2011 19:55 40617.83 2011-03-15 19:54:59
#  8   8  3/17/2011 7:55 40619.33 2011-03-17 07:54:59
#  9   9  3/18/2011 4:04 40620.17 2011-03-18 04:03:59
#  10 10  3/18/2011 4:04 40620.17 2011-03-18 04:03:59

How to convert Excel date format to proper date in R

You don't need to use lubridate for this, the base function as.Date handles this type of conversion nicely. The trick is that you have to provide the origin, which in Excel is December 30, 1899.

as.Date(42705, origin = "1899-12-30")
# [1] "2016-12-01"

If you want to preserve your column types, you can try using the read_excel function from the readxl package. That lets you load an XLS or XLSX file with the number formatting preserved.

Convert datetime from Excel to R

First, you need to read the datetime column as "date" in order to be able to convert it. For example, if you have the following excel file:

ID    Name     Date
1     aaa      9/14/2017  7:58:58 AM
2     bbb      2/23/2017  5:22:01 PM
3     bbb      7/10/2017  10:37:19 PM

You would need to read it as followS:

excel_data <- read_excel("test.xls", col_types = c("numeric", "text", "date"))

If you have a long list of columns, you can just specify the col_types as guess, like this:

excel_data <- read_excel("test.xls", col_types = "guess")

---

Now, to convert into to a readable datetime in R, you would need to use as.POSIXct with the origin value as 1899-12-30, as follows:

excel_data$Date <- as.POSIXct(excel_data$Date,
                              origin="1899-12-30",
                              tz="GMT")

Hope it helps.

excel date time conversion to POSXIct

One needs to know the key differences between the two time systems here.

In Excel 43306.29 means 43306 days from Jan 1, 1900 (day 1) and the 0.29 is the fraction of the day (about 7 hours here).

R uses Unix time keeping standard so it tracks the number of seconds from the Jan 1, 1970 (the beginning of time for a Unix programmer).

So in order to convert from Excel to R, you need to covert the number of days from the origin to the number of seconds (60 sec * 60 min * 24 hours).

as.POSIXct(43306.29*3600*24 , origin="1899-12-30")
#"2018-07-25 02:57:36 EDT"

as.POSIXct(43306.29*3600*24 , origin="1899-12-30", tz="UTC")
#"2018-07-25 06:57:36 UTC"

Note: Windows and Excel assumes there was a leap year in 1900 which there wasn't so the origin needs a correction to Dec 30, 1899.

Convert serial date

You can handle this in two steps in any order.

Convert numbers to date

new_date <- as.Date(as.numeric(date), origin = "1899-12-30")

This will return warnings for dates that cannot be converted to numeric.

Convert remaining dates using "dmy" format

new_date[is.na(new_date)] <- as.Date(date[is.na(new_date)], "%d/%m/%Y")
#Or using lubridate's dmy
#new_date[is.na(new_date)] <- lubridate::dmy(date[is.na(new_date)])

new_date
#[1] "2020-04-21" "2020-04-24" "2020-05-01" "2020-05-17" "2020-05-17" "2020-05-18"
#[7] "2020-05-19" "2020-05-18" "2020-05-22"

Reformat Excel numeric date to R date

In excel, number 40182 gives the date 04-01-2010 when formated as date.
So I think you are looking for this:

format(as.Date(40182, origin = "1899-12-30"), '%b-%Y')

which gives:

[1] "Jan-2010"

hope it helps!!!

how to convert number into date and timestamp in R?

In case the numbers come from Excel you can use as.POSIXct:

as.POSIXct(c(44245, 44242, 44245.66, 44231) * 86400, origin = "1899-12-30", tz="GMT")
#[1] "2021-02-18 00:00:00 GMT" "2021-02-15 00:00:00 GMT"
#[3] "2021-02-18 15:50:24 GMT" "2021-02-04 00:00:00 GMT"

---

Related Topics