Date Conversion From PDT/PST (to) IST

[pavin]

Hi All,

I'm looking for a Date(in text format) to convert from PDT/PST to IST using Excel/VBA. Kindly help. Thank you.

Example:
"Tue Jun 30 11:30:00 PDT 2015"

Result:
"Wed Jul 01 12:00:00 IST 2015"

 

[Scott Huish]

Function PDTtoIST(r As String) As String
Dim d, dv
d = Split(r)
dv = DateValue(d(1) & " " & d(2) & " " & d(5)) + TimeValue(d(3))
dv = DateAdd("n", 1470, dv)
PSTtoIST = Format(dv, "ddd mmm dd hh:mm:ss ""IST"" yyyy")
End Function

 

[pavin]

Amazing....works fine. Many Thanks

I created something like the following.

=TEXT(CONCATENATE(TEXT(DATE(RIGHT(A2,4),MONTH(1&LEFT(MID(A2,5,3),3)),MID(A2,9,2)),"mm/dd/yyyy")&" "&TEXT(TIMEVALUE(MID(A2,FIND(" ",A2,9)+1,8)),"hh:mm:ss"))+TIME(12,30,0),"ddd mmm dd hh:mm:ss AM/PM ""IST"" yyyy")

 

[Rick Rothstein]

Amazing....works fine.

I am wondering if Scott's code will always work correctly for you? Your thread title indicates the date/time being converted could be either daylight saving time or standard time and that the date/time it is being converted to is always standard date/time. Scot's code does not handle both standard and daylight saving time as the value to convert from. Did you really need to distinguish between the two? If so, are you really only converting to a standard time value only? I would also note that although Scott's function is named to suggest it is converting standard time to standard time, it appears to actually be converting daylight savings time to standard time as that is what your Example/Result says your two times are.

 

[Scott Huish]

Good call.

Function PTtoIST(r As String) As String
Dim d, dv
d = Split(r)
dv = DateValue(d(1) & " " & d(2) & " " & d(5)) + TimeValue(d(3))
dv = DateAdd("n", IIf(d(4) = "PDT", 1470, 1530), dv)
PTtoIST = Format(dv, "ddd mmm dd hh:mm:ss ""IST"" yyyy")
End Function

Excel 2010
	A	B
1	Tue Jun 30 11:30:00 PDT 2015	Wed Jul 01 12:00:00 IST 2015
2	Tue Jun 30 11:30:00 PST 2015	Wed Jul 01 13:00:00 IST 2015
Sheet1

Cell Formulas
Range	Formula
B1	=PTtoIST(A1)
B2	=PTtoIST(A2)