提问人：Statto 提问时间：10/27/2023 最后编辑：Mayukh BhattacharyaStatto 更新时间：10/27/2023 访问量：101
如何将一组UTC日期/时间转换为GMT/BST
How to convert a set of UTC dates/times into GMT/BST
问：
我正在尝试将一组以世界协调时间 （UCT） 格式存储的日期/时间转换为伦敦时间，每年在英国夏令时 （BST） 和格林威治标准时间 （GMT） 之间切换。
让我们以 2023 年为例，清楚地了解英国时间系统的运作方式。
2023 年 3 月 26 日（最后一个周日）01：00：00 UTC（格林威治标准时间 01：00：00），时钟向前移动一小时，这意味着时间变为 02：00：00 BST。
2023 年 10 月 29 日（最后一个周日）01：00：00 UTC（英国夏令时 02：00：00），时钟倒退一小时，这意味着时间变为 01：00：00 GMT。
我写的公式几乎实现了这一点，除了它不会在上述时间向前或向后跳跃，因为它是一秒钟。B2
=IF([@[Date/Time UTC]]="","",
[@[Date/Time UTC]]
+
IF(
PRODUCT(
[@[Date/Time UTC]]
-
DATE(
YEAR([@[Date/Time UTC]]),
{4,11},
1-WEEKDAY(DATE(YEAR([@[Date/Time UTC]]),{4,11},0))
)-1/24
)
<0,
1)
/24)
目前，当是 2023 年 3 月 26 日时，时间必须是 01：00：01 UTC（01：00：01 GMT）才能触发向前跳转，因此变为 02：00：01 BST。
当是 2023 年 10 月 29 日时，时间必须是 01：00：01 UTC（英国夏令时 02：00：01）才能触发向后跳转，从而变为 01：00：01 GMT。
如何调整配方使其完美运行？
答：
1赞
Ron Rosenfeld
10/27/2023
#1
要将 UTC 转换为 BST，您需要检查 UTC 时间是否在 BST 间隔内，如果是，则添加和小时，如果不是，请不要理会。
由于浮点数的小数部分不准确，您还需要进行一些舍入。
如果您有该功能，此公式将起作用。如果您有早期版本的 Excel，则需要扩展该函数。LET
=LET(
t, MROUND([@[Date/Time UTC]], TIME(0, 0, 1)),
m, DATE(YEAR(t), 4, 1),
bs, m - WEEKDAY(m) + 1 + TIME(1, 0, 0),
o, DATE(YEAR(t), 11, 1),
be, o - WEEKDAY(o) + 1 + TIME(1, 0, 0),
IF(AND(t >= bs, t < be), t + TIME(1, 0, 0), t)
)
t是四舍五入到最接近的秒数的 UTC 日期/时间。m是 UTC 年的 4 月 1 日bd是 BST 的开始日期，即相对于mo并遵循与计算 BST 结尾类似的逻辑。be
2赞
Mayukh Bhattacharya
10/27/2023
#2
您可以尝试以下公式来完成从 到 using 函数的转换，包装在UTCBSTWORKDAY.INTL( )LET( )
• 细胞中使用的配方B2
=LET(
α, [@[Date/Time UTC]],
φ, WORKDAY.INTL(DATE(YEAR(α),4,1),-1,"1111110"),
ε, WORKDAY.INTL(DATE(YEAR(α),11,1),-1,"1111110"),
α+((α>=φ+1/24)*(α<ε+1/24))/24)
"1111110"其中表示非工作日，表示工作日，因此表示从“到”开始仅是一周中的工作日。10MondaySundaySunday
φ-->WORKDAY.INTL(DATE(YEAR(α),4,1),-1,"1111110")它返回紧接在 .1stAprilSundayMarch
=φ+1/24--> 返回 中的最后一个。1 AMSundayMarch
ε-->WORKDAY.INTL(DATE(YEAR(α),11,1),-1,"1111110")同样返回紧接在最后一个工作日之前的工作日（星期日）1stNovemberSundayOctober
ε+1/24--> 返回 中的最后一个。1 AMSundayOctober
编辑：快速修复现有公式
• 细胞中使用的配方B2
=LET(
α, [@[Date/Time UTC]],
φ, YEAR(α),
ε, {4,11},
σ, DATE(φ, ε, 1 - WEEKDAY(DATE(φ, ε, 0)))+1/24,
α + (α>=TAKE(σ,,1))*(α<DROP(σ,,1))/24)
评论