[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
Medium Priority
943 Views
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
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
• 3
• 2

LVL 101

Expert Comment

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
Else

mlmcc
0

Author Comment

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

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

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

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

WhilePrintingRecords;
Global DateVar DSTStartDate;
AND
Else
``````
0

Author Closing Comment

ID: 35200407
Worked perfectly! Thanks!
0

## Featured Post

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…