如何在R中将Excel日期格式转换为正确的日期格式

Question

我正在使用一个csv文件，不幸的是它记录日期时间时使用了42705这种数字格式，但应该是01/12/2016。

我想在R中使用lubridate或其他软件包将其转换为正确的格式。是否有一个函数可以处理它？

- elksie5000

---

这个可能对你有帮助：https://dev59.com/eGIk5IYBdhLWcg3wi-tM - count

非常感谢您的快速回复。 - elksie5000

Answer 1

您不需要使用lubridate来完成此操作，基础函数as.Date可以很好地处理这种类型的转换。诀窍在于您必须提供Excel中的起始日期，即1899年12月30日。

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

如果您想保留列类型，可以尝试使用readxl包中的read_excel函数。这允许您加载一个带有数字格式的XLS或XLSX文件。

编辑：相关的XKCD。

- Andrew Brēza

---

谢谢您。我对R还很陌生。这个方法非常有效。 - elksie5000

非常欢迎。我正在编辑我的回复，因为我意识到原始代码略有偏差。在你将这段代码用于你的项目之前，请给我一分钟的时间 :) - Andrew Brēza

实际上，我只是在Excel中输入了01/12/2016并将其转换为数字。我得到的值是42381而不是42705。使用42381在 as.Date 函数中可以让你回到1/12/2016。你确定你从Excel中正确地复制了这个数字吗？ - Andrew Brēza

在法语版的Excel中，对于带有小时的日期，最佳解决方案非常迷人: as_datetime(Date, origin = "1969-12-31 24:00:00") - Clément LVD

对于那些想知道 "1899-12-30" 这个日期是怎么来的以及这是否正确的人们，似乎在某些 Excel 版本中，Microsoft 使用了 1900 年作为参考日期，而在其他版本中使用了 1904 年！因此建议您从其他来源确认日期。来源：https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 - Mehrad Mahmoudian

Answer 2

这里有另一种使用 janitor 和 tibble 包的方法：

install.packages("janitor")
install.packages("tibble")

library(tibble)
library(janitor)

excel_numeric_to_date(as.numeric(as.character(YourDate)), date_system = "modern")    

- Reza Rahimi

---

convert_to_date()是janitor软件包中的一个函数，可用于在Excel数字日期和实际日期混合的情况下将其转换为日期格式。 - userLL

Answer 3

openxlsx 包还允许 xls date 转换：

openxlsx::convertToDate(42705)
[1] "2016-12-01"

正如 @Suren 建议的那样，convertToDateTime 允许进行datetime转换:

openxlsx::convertToDateTime(42705.5)
[1] "2016-12-01 12:00:00"

- Waldi

---

如果convertToDateTime是日期时间类型，则进行转换。 - kangaroo_cliff

由于某种原因，这个函数只能将数据转换为日期，而不能转换为日期时间，即使我在数据框中提供了十进制数。来自“janitor”的函数运行良好，我没有遇到同样的问题。 - K Bro

@K 兄弟，我又测试了上述示例，在 R 4.2 上仍会转换为 datetime。 - Waldi

Answer 4

正如所说，非常好的选择：

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

openxlsx::convertToDate(42705)

另一种方式也可能是：

format(as.Date(as.Date("1899-12-30") + 42705, "%d-%m-%Y"), "%d-%m-%Y")

注意：可以在写有%d-%m-%Y的地方更改输出格式。

首先，如果输入数据为字符型，请将其转换成数值型 as.numeric，或在公式中进行转换。

format(as.Date(as.Date("1899-12-30") + as.numeric( number formatted as character), "%d-%m-%Y"), "%d-%m-%Y")

- CONSULT FIV

Answer 5

如果您正在使用data.table包，您可以使用as.IDate()来实现：

require(data.table)

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

这里的作用类似于base::as.Date()。

- andschar

Answer 6

根据Reza提供的清洁工解决方案，如果你有一组混合了Excel数值日期和格式不正确的dmy日期，这个方法可以解决：

df$Procedure.Date <- convert_to_date(df$Procedure.Date, character_fun = lubridate::dmy, string_conversion_failure = "warning")

character_fun决定日期条目的格式，string_conversion_failure参数指定您将收到警告，但转换将继续进行并保存在您的数据中。

- Tom