将日期从数字格式的excel转换为日期格式python
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31359150/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert date from excel in number format to date format python
提问by user2728024
I am reading data from excel and manipulating the data using python. But dates are coming as integers. How can I convert the dates back to date format?
我正在从 excel 读取数据并使用 python 操作数据。但是日期以整数形式出现。如何将日期转换回日期格式？
5/15/2015 is coming as 42139.00
2015 年 5 月 15 日是 42139.00
采纳答案by saeedgnu
from datetime import datetime
excel_date = 42139
dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + excel_date - 2)
tt = dt.timetuple()
print dt
print tt
As mentioned by J.F. Sebastian, this answer only works for any date after 1900/03/01
正如 JF Sebastian 所提到的，此答案仅适用于 1900/03/01 之后的任何日期
EDIT: (in answer to @R.K)
编辑：（回答@RK）
If your excel_dateis a float number, use this code:
如果您excel_date是浮点数，请使用以下代码：
def floatHourToTime(fh):
h, r = divmod(fh, 1)
m, r = divmod(r*60, 1)
return (
int(h),
int(m),
int(r*60),
)
excel_date = 42139.23213
dt = datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(excel_date) - 2)
hour, minute, second = floatHourToTime(excel_date % 1)
dt = dt.replace(hour=hour, minute=minute, second=second)
回答by zveryansky
The module xlrdprovides a function xldate_as_tupleto convert Excel's numerical date format to a tuple (year, month, day, hour, minute, nearest_second).
该模块xlrd提供了xldate_as_tuple将 Excel 的数字日期格式转换为 tuple的函数(year, month, day, hour, minute, nearest_second)。
You can then use datetime.datetimeto convert the tuple into a datetime-object.
然后，您可以使用datetime.datetime将元组转换为datetime-object。
from datetime import datetime
import xlrd
excel_date = 44032
python_date = datetime(*xlrd.xldate_as_tuple(excel_date, 0))
