Converting a USA-formatted date with PST/PDT into an Italy-formatted date with CET/CEST

When you import comma-separated values (CSV files) into an Excel sheet, the dates are formatted according to your operating system settings. Unlike manually entered values, imported dates whose format is not recognized are converted to text that can’t be converted directly into dates with another format later on; so a custom formula or VBA function is needed.

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

'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.