[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Need a formula for Daylight Savings Time

Posted on 2011-03-16
5
Medium Priority
?
952 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
  • 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses
Course of the Month7 days, 19 hours left to enroll

608 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