Thursday, March 8, 2012

DateTime conversion from GST to EST

I am trying to find a formula to convert the GST to local time EST as well as account for daylight savings time.

We had a formula that was working until this year when the dates on DST changed. (Probably redundant but changed from the 1st Sunday/April thru last Sunday/Oct to 2nd Sunday/March thru 1st Sunday/Nov).

This is what we were doing before; ugly but it worked...

Date
date({PROBSUMMARY1.OPEN_TIME})

DayLite
if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

Time
time({PROBSUMMARY1>OPEN_TIME})-(({@.DayLite})*3600)

DateAdjATL
if Time({PROBSUMMARY1.OPEN_TIME})<=TimeValue(05,00,00)then
DateTimeValue({@.Date}-1,{@.Time}) else DateTimeValue ({@.Date},{@.Time})

We are currently hard coding the dates and we all know how inefficient that is... ANY HELP IS GREATLY APPRECIATED!!!Looks like something is missing...
DayLite
if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

if that is correct, you may need to change the months to

if Month({PROBSUMMARY1.OPEN_TIME}) in 3 to 11 then else 5

Hard to tell....

since you have an if, but no value if the if is true.

if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

for example, if Month({prob...}) in 4 to 10 then (What ?) else 5

No comments:

Post a Comment