asked on

Need a formula for Daylight Savings Time

I am having trouble creating a formula for Daylight Savings Time. I have a DateTime field that is in GMT (Greenwich Mean Time). I have come up with a formula which will display the time depending on the time zone the account is in. However, now that Daylight Savings Time has started, my times are an hour off. I can go in and correct the formula to subtract an hour less, but I'm just going to have to correct the report again in the fall. I would rather the formula be created to handle DST on it's own automatically.

DST starts on the second Sunday of March and ends on the first Sunday in november.

Mike McCracken

This will work for this year. I'll work on one that works in general.
Use the appropriate differences based on time zone

If {YourDateTimeField} >= DateTime(2011,3,13,2,0,0)
AND
{tblHoursWorked2010.Started} < Date(2011,11,13,2,0,0) then
DateAdd('h',-3,{tblHoursWorked2010.Started})
Else
DateAdd('h',-4,{tblHoursWorked2010.Started})

mlmcc

ans0123

ASKER

This formula works for now, but is there a way to make the formula automatically update for the time change without me having to modify the report twice a year? Daylight Savings Time begins on the 2nd Sunday of March every year and ends on the 1st Sunday in November.

Mike McCracken

I am sure there is. I meant to try to do it but forgot. Let me try a few things.

mlmcc

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ans0123

ASKER

Worked perfectly! Thanks!