使用VBA将UTC转换为PST/PDT/CET和CEST的时间
我有一个基于协调世界时时间的一年时间和日期的云(Col. A)，格式如下(实际上我有30分钟的间隔)，从一年的第一天到年底：
01.01.2019 00:00
01.01.2019 00:30
.
.
.
31.12.2019 11:00
31.12.2019 11:30现在，我想要将冬季时间时的UTC时间转换为CET时间，以及夏季时间时的CEST时间(我希望在Col.B中包含信息)。我可以用Excel Formula很容易地做到这一点，但由于我的文件足够大，我必须使用VBA代码。有什么建议吗?我该怎么做？我的问题是如何通过反之亦然的方式将夏季时间转换为冬季时间。
回答 1
Stack Overflow用户
发布于 2019-04-27 22:50:05
关于时区转换本身，维基百科说(https://en.wikipedia.org/wiki/Central_European_Summer_Time#Period_of_observation)：
自1996年以来，欧洲夏季时间一直在协调世界时3月最后一个星期日的01:00 (欧洲中部时间02:00和欧洲中部时间03:00 )和10月最后一个星期日的01:00 (欧洲标准时间01:00
如果我没看错的话，datetime只有在以下情况下才能是CET：
- 在(
<)世界标准时间3月最后一个星期日01:00UTC(即欧洲中部夏令时开始时)之前或之后( - )或在(
>=) 10月最后一个星期日世界协调时01:00 (即欧洲中部夏令时开始时)
我不清楚你的源数据是什么样子的。(它看起来像是由空格分隔的两个日期时间，这意味着每一行实际上包含一个字符串。如果是这样，您可能需要沿着分隔空格将字符串拆分为两个字符串，然后将这两个字符串转换为两个日期时间。这可能可以通过VBA中的LEFT、RIGHT、DATEVALUE函数来完成。)
举个例子，我以半小时为间隔用datetimes填充了我的工作表"Sheet1"的range A1:A17520 (类似于您所展示的)：
然后使用下面的代码转换A列中的值(并将转换后的值放入同一张表的B列中)：
Option Explicit
Private Sub ConvertUtcDatesInColumnAToCETorCEST()
Dim sourceSheet As Worksheet
Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
Dim lastSourceRow As Long
lastSourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
Dim datesFromSheet() As Variant
datesFromSheet = sourceSheet.Range("A1", sourceSheet.Cells(lastSourceRow, "A"))
Dim outputArray() As Date
ReDim outputArray(1 To UBound(datesFromSheet, 1), 1 To 1)
Dim rowIndex As Long
For rowIndex = LBound(datesFromSheet, 1) To UBound(datesFromSheet, 1)
outputArray(rowIndex, 1) = ConvertUtcDateTimeToCETorCEST(datesFromSheet(rowIndex, 1))
Next rowIndex
sourceSheet.Range("B1").Resize(UBound(outputArray, 1), UBound(outputArray, 2)).Value = outputArray
End Sub
Private Function LastSundayOfSomeMonthAndYear(ByVal someMonth As Long, ByVal someYear As Long) As Date
Dim lastDateInMonth As Date
lastDateInMonth = Application.EoMonth(DateSerial(someYear, someMonth, 1), 0)
Dim lastSundayInMonth As Date
LastSundayOfSomeMonthAndYear = lastDateInMonth - Weekday(lastDateInMonth, vbSunday) + 1
End Function
Private Function ConvertUtcDateTimeToCETorCEST(ByVal someUtcDateTime As Date) As Date
Dim startOfCESTinUTC As Date
startOfCESTinUTC = LastSundayOfSomeMonthAndYear(someMonth:=3, someYear:=Year(someUtcDateTime)) + TimeSerial(1, 0, 0)
Dim endOfCESTinUTC As Date
endOfCESTinUTC = LastSundayOfSomeMonthAndYear(someMonth:=10, someYear:=Year(someUtcDateTime)) + TimeSerial(1, 0, 0)
Dim isCET As Boolean
isCET = someUtcDateTime < startOfCESTinUTC Or someUtcDateTime >= endOfCESTinUTC ' Not sure if end is inclusive or exclusive
Dim hoursToAdd As Date
If isCET Then hoursToAdd = TimeSerial(2, 0, 0) Else hoursToAdd = TimeSerial(3, 0, 0)
ConvertUtcDateTimeToCETorCEST = someUtcDateTime + hoursToAdd
End Function这给了我一些类似的东西：
需要注意的是，这种方法的效率非常低，原因有几个：
- 每次调用函数
ConvertUtcDateTimeToCETorCEST时都会重新计算变量startOfCESTinUTC和endOfCESTinUTC(在我的例子中大约是17.5k次)。因为我们所有的datetimes都是在同一年，所以startOfCESTinUTC和endOfCESTinUTC的值不会改变(所以实际上在For循环之前，变量startOfCESTinUTC和endOfCESTinUTC应该只确定一次)。 - 如果你提前知道并确定你的数据是排序的，并且是以半小时为间隔的，你可以做一些数学运算，计算出哪一行必须开始和结束。有了这些知识，您就不再需要检查datetime在
For循环中是CET还是CEST。如果使用三个For循环(循环1:第一行to行CET结束开，循环2:行CEST开始于to行CEST结束，循环3:行CET开始于to最后一行)，则可以将所有值转换为CET (循环1，循环3)和CEST (循环2)，而无需实际检查其值是什么。这使得代码更专业(更快)，但可重用性也较低。
现代计算机(相对)速度快。上面的代码在我的机器上在不到2秒的时间内为我转换了17.5k的日期时间。您可能还希望检查代码是否正确处理CET和CEST的边界。
https://stackoverflow.com/questions/55880175
复制相似问题
腾讯云开发者
