请求协助修改VBA宏：将时间转换为PST而非UTC

Hey there! Converting local time to Pacific Time (which includes both PST and PDT for daylight saving) is a bit more nuanced than UTC conversion. Here are two practical approaches based on your original code:

1. Fixed PST Offset (Ignore Daylight Saving) #

If you specifically need only PST (UTC-8, regardless of the season), you can adjust your existing code by subtracting the fixed 8-hour offset from UTC:

Dim dt As Object, utcTime As Date, pstTime As Date
Set dt = CreateObject("WbemScripting.SWbemDateTime")

' Convert local time to UTC first (using your original logic)
dt.SetVarDate Now
utcTime = dt.GetVarDate(False)

' Subtract 8 hours to get PST (UTC-8)
pstTime = DateAdd("h", -8, utcTime)

' Optional: Display the result
MsgBox "Local Time: " & Format(Now, "yyyy-mm-dd hh:mm:ss AM/PM") & vbCrLf & _
       "PST Time: " & Format(pstTime, "yyyy-mm-dd hh:mm:ss AM/PM")

2. Robust Pacific Time (Auto-Handles PST/PDT) #

For a solution that automatically switches between PST and PDT based on the date (following Windows' time zone rules), use the Win32_TimeZone WMI class to get the correct offset dynamically:

Dim dt As Object, localTime As Date, pacificTime As Date
Dim timeZone As Object, baseBias As Integer, dstBias As Integer, totalBias As Integer

localTime = Now

' Fetch Pacific Time zone details from Windows
Set timeZone = GetObject("winmgmts:").Get("Win32_TimeZone='Pacific Standard Time'")
baseBias = timeZone.Bias ' Base offset (minutes) from UTC for PST (480 = 8 hours)
dstBias = timeZone.DaylightBias ' DST adjustment (-60 minutes = -1 hour for PDT)

' Calculate total offset from UTC (account for DST if applicable)
If timeZone.IsDaylightSavingTime(localTime, "") Then
    totalBias = baseBias + dstBias
Else
    totalBias = baseBias
End If

' Convert local time to UTC (reusing your original method)
Set dt = CreateObject("WbemScripting.SWbemDateTime")
dt.SetVarDate localTime

' Subtract total offset minutes from UTC to get Pacific Time
pacificTime = DateAdd("n", -totalBias, dt.GetVarDate(False))

' Optional: Display formatted result
MsgBox "Local Time: " & Format(localTime, "yyyy-mm-dd hh:mm:ss AM/PM") & vbCrLf & _
       "Pacific Time: " & Format(pacificTime, "yyyy-mm-dd hh:mm:ss AM/PM")

Key Notes: #

• The Win32_TimeZone class uses the official Windows time zone name "Pacific Standard Time", which covers both PST and PDT.
• Bias is the number of minutes local standard time is behind UTC (480 for PST). DaylightBias reduces this offset by 60 minutes during DST, resulting in UTC-7 for PDT.
• The IsDaylightSavingTime method checks if the given local time falls within DST, ensuring the correct offset is applied automatically.

内容的提问来源于stack exchange，提问作者steamboy naratsu