Solved

Need a formula for Daylight Savings Time

Posted on 2011-03-16
5
931 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 125 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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
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: …

695 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