Improve company productivity with a Business Account.Sign Up

x
?
Solved

Need a formula for Daylight Savings Time

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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. …
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…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…

606 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