Python日期时间到Excel串行日期转换

发布于 2025-01-12 21:28:35 字数 612 浏览 0 评论 0原文

以下代码将字符串转换为时间戳。时间戳结果为：1646810127。

但是，如果我使用 Excel 将此日期和时间转换为浮点数，我会得到：44629,34。我需要 Python 脚本的 Excel 输出。

我尝试使用一些不同的日期时间字符串来查看两个数字之间是否存在任何模式，但似乎找不到任何模式。

关于如何让代码输出 44629,34 有什么想法吗？

非常感谢

import datetime

date_time_str = '2022-03-09 08:15:27'
date_time_obj = datetime.datetime.strptime(date_time_str, '%Y-%m-%d %H:%M:%S')

print('Date:', date_time_obj.date())
print('Time:', date_time_obj.time())
print('Date-time:', date_time_obj)

print(date_time_obj.timestamp())

>>output:
Date: 2022-03-09
Time: 08:15:27
Date-time: 2022-03-09 08:15:27
1646810127.0

原文

The following code converts a string into a timestamp. The timestamp comes out to: 1646810127.

However, if I use Excel to convert this date and time into a float I get: 44629,34.
I need the Excel's output from the Python script.

I have tried with a few different datetime strings to see if there is any pattern in between the two numbers, but cannot seem to find any.

Any thoughts on how I get the code to output 44629,34?

Much appreciated

import datetime

date_time_str = '2022-03-09 08:15:27'
date_time_obj = datetime.datetime.strptime(date_time_str, '%Y-%m-%d %H:%M:%S')

print('Date:', date_time_obj.date())
print('Time:', date_time_obj.time())
print('Date-time:', date_time_obj)

print(date_time_obj.timestamp())

>>output:
Date: 2022-03-09
Time: 08:15:27
Date-time: 2022-03-09 08:15:27
1646810127.0

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北城挽邺 2025-01-19 21:28:35

计算日期时间对象的 timedelta 与 Excel 的“零日”，然后将 timedelta 的总秒数除以一天中的秒数以获得 Excel 序列日期：

import datetime

date_time_str = '2022-03-09 08:15:27'
UTC = datetime.timezone.utc

dt_obj = datetime.datetime.fromisoformat(date_time_str).replace(tzinfo=UTC)
day_zero = datetime.datetime(1899,12,30, tzinfo=UTC)

excel_serial_date = (dt_obj-day_zero).total_seconds()/86400

print(excel_serial_date)
# 44629.3440625

注意：我在此处将时区设置为 UTC 以避免任何歧义 - 调整根据需要。

由于问题被标记为 pandas，您可以在这里做同样的事情，只是您不需要设置 UTC，因为 pandas 默认为天真的日期时间假定 UTC：

import pandas as pd

ts = pd.Timestamp('2022-03-09 08:15:27')

excel_serial_date = (ts-pd.Timestamp('1899-12-30')).total_seconds()/86400

print(excel_serial_date)
# 44629.3440625

另请参阅：

背景：以 1899 年 12 月 30 日为基地背后的故事是什么date?
逆运算：用 pandas 转换 Excel 样式日期

calculate the timedelta of your datetime object versus Excel's "day zero", then divide the total_seconds of the timedelta by the seconds in a day to get Excel serial date:

import datetime

date_time_str = '2022-03-09 08:15:27'
UTC = datetime.timezone.utc

dt_obj = datetime.datetime.fromisoformat(date_time_str).replace(tzinfo=UTC)
day_zero = datetime.datetime(1899,12,30, tzinfo=UTC)

excel_serial_date = (dt_obj-day_zero).total_seconds()/86400

print(excel_serial_date)
# 44629.3440625

Note: I'm setting time zone to UTC here to avoid any ambiguities - adjust as needed.

Since the question is tagged pandas, you'd do the same thing here, only that you don't need to set UTC as pandas assumes UTC by default for naive datetime:

import pandas as pd

ts = pd.Timestamp('2022-03-09 08:15:27')

excel_serial_date = (ts-pd.Timestamp('1899-12-30')).total_seconds()/86400

print(excel_serial_date)
# 44629.3440625