Converting a USA-formatted date with Pacific time into an Italian-formatted date with Italy time

DATE(MID(B2; 7; 4); LEFT(B2; 2); MID(B2; 4; 2)) + TIMEVALUE(RIGHT(B2; 5))

So my function ITALIANDATETIME applies the rules in force since 2007 in the U.S.A. West Coast countries where the local time changes from PST to PDT to the second Sunday in March and the reversal from PDT to PST to the first Sunday in November. We already know the time offset is not always the same because clock changes in the two zones (U.S.A. West Coast countries and Italy or any else Central European country) occurs in different moments.

Function ITALIANDATETIME(dt As String) As Variant

‘dt is the date in the string format including PST or PDT
Y = Mid(dt, 7, 4)
M = Left(dt, 2)
D = Mid(dt, 4, 2)
H = Right(dt, 5)

dts = DateSerial(Y, M, D) + TimeValue(H)

Select Case M
Case 3 ‘March

For i = 1 To 7
If Weekday(DateSerial(Y, 3, i)) = 1 Then
SUN = i + 7 ‘On the 2nd Sunday of March at 2:00 PST the clock moves one hour forward (3:00 PDT)
Exit For
End If
Next

For i = SUN To 31 Step 7
SAT = i – 1 ‘When it’s the last Sunday of March at 2:00 CET/3:00 CEST it’s Saturday at 18:00 PDT
Next

If DateSerial(Y, 3, SUN) + TimeSerial(2, 0, 0) < dts And dts < DateSerial(Y, 3, SAT) + TimeSerial(18, 0, 0) Then
ITALIANDATETIME = dts + TimeSerial(8, 0, 0)
Else
ITALIANDATETIME = dts + TimeSerial(9, 0, 0)
End If

Case 10 ‘October

For i = 25 To 31
If Weekday(DateSerial(Y, 10, i)) = 1 Then
SAT = i – 1 ‘When it’s the last Sunday of October at 3:00 CEST / 2:00 CET it’s Saturday at 18:00 PDT
Exit For
End If
Next

If DateSerial(Y, 10, SAT) + TimeSerial(18, 0, 0) <= dts Then
ITALIANDATETIME = dts + TimeSerial(8, 0, 0)
Else
ITALIANDATETIME = dts + TimeSerial(9, 0, 0)
End If

Case 11 ‘November

For i = 1 To 7
If Weekday(DateSerial(Y, 11, i)) = 1 Then
SUN = i ‘On the 1st Sunday of November at 2:00 PDT the clock moves one hour backward (1:00 PST)
Exit For
End If
Next

If dts < DateSerial(Y, 11, SUN) + TimeSerial(2, 0, 0) Then
ITALIANDATETIME = dts + TimeSerial(8, 0, 0)
Else
ITALIANDATETIME = dts + TimeSerial(9, 0, 0)
End If

Case Else ‘The other months

ITALIANDATETIME = dts + TimeSerial(9, 0, 0)

End Select

End Function

I was inspired by this @Lampo10‘s question.

Try my VBA function ITALIANDATETIME through the .XLSM file I uploaded.

The following formula that converts a U.S.A. date with local time (mm/gg/aaaa hh:mm) in cell B2 into an Italian formatted one with the same time (gg/mm/aaaa hh:mm) wouldn’t take into account the different time zones: DATE(MID(B2; 7; 4); LEFT(B2; 2); MID(B2; 4; 2)) + TIMEVALUE(RIGHT(B2; 5)) So my function ITALIANDATETIME applies the rules in force since 2007 in the U.S.A. West Coast countries where the local time changes from PST to PDT to the second Sunday in March and the reversal from PDT to PST to the first Sunday in November. We already know the time offset is not always the same because clock changes in the two zones (U.S.A. West Coast countries and Italy or any else Central European country) occurs in different moments. Function ITALIANDATETIME(dt As String) As Variant