Forum Discussion
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.