?
Solved

Need a formula for Daylight Savings Time

Posted on 2011-03-16
5
Medium Priority
?
935 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:ans0123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35149713
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
0
 

Author Comment

by:ans0123
ID: 35183297
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35184353
I am sure there is.  I meant to try to do it but forgot.  Let me try a few things.

mlmcc
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35184447
Here are the formulas

In the adjustment, just change the -3/-4 to the appropriate difference

THe formula for the 2nd Sunday and 1 Sunday was supplied by
Ken Hamady
http://www.kenhamady.com/form23.shtml

mlmcc
Formula for Setting the START of DST
WhilePrintingRecords;
 DateVar D:= Date(Year({tblHoursWorked2011.Started}),3,1);  //Today's Date or any date field
 Numbervar DOW:= 1; //The day of week you want with 1 being Sunday, 2 = Monday, etc
 NumberVar Week:= 2; // The Week you want, 1 being the FIRST Monday of the month, 2 being the second, etc
 DateVar BOM:= D - Day(D)+(8-DOW);
 DateVar BOW:= BOM - DayOfWeek(BOM) + DOW  + (7*(Week-1));
Global DateVar DSTStartDate := BOW



Formula for Setting the END of DST
WhilePrintingRecords;
 DateVar D:= Date(Year({tblHoursWorked2011.Started}),11,1);  //Today's Date or any date field
 Numbervar DOW:= 1; //The day of week you want with 1 being Sunday, 2 = Monday, etc
 NumberVar Week:= 1; // The Week you want, 1 being the FIRST Monday of the month, 2 being the second, etc
 DateVar BOM:= D - Day(D)+(8-DOW);
 DateVar BOW:= BOM - DayOfWeek(BOM) + DOW  + (7*(Week-1));

Global DateVar DSTEndDate := BOW


Using them to adjust your time
WhilePrintingRecords;
Global DateVar DSTStartDate;
If {YOURDATETIMEFIELD} >= DateTime({@AdjustForDSTStartDate},Time(2,0,0))
      AND
   {YOURDATETIMEFIELD} < DateTime({@AdjustForDSTEndDate},Time(2,0,0)) then
    DateAdd('h',-3,{YOURDATETIMEFIELD})
Else
    DateAdd('h',-4,{YOURDATETIMEFIELD})

Open in new window

0
 

Author Closing Comment

by:ans0123
ID: 35200407
Worked perfectly! Thanks!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question