[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Need a formula for Daylight Savings Time

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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