DayLightSavings ( TargetDate ; TargetTime ; Hours )

Day Light Saving Time

Average rating: 4.0 (41 votes) Log in to vote

Timothy Brown - Show more from this author
Antidote Solutions
http://www.antidotesolutions.com

Sample input:

DayLightSavings ( 11/2/2008 ; 7:00 AM ; -24 )

Sample output:

11/1/2008 6:00 AM

should daylight savings occur during the -24 hour period, it will adj the output time by an hour.

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Let(
[
begdate = TargetDate;
y = Year(begdate);
begtime = TargetTime;

begTS = GetAsTimestamp( begdate & " " & GetAsTime(begtime)) ;
endTS = GetAsTimestamp( begdate & " " & GetAsTime(begtime)) + (hours * 3600);   //Hours can be negative
day1 =    // current rules - daylight is always 2nd Sunday in March
Case(
DayOfWeek ( Date(3;8; y )) = 1; 8;      // Test for each Sunday
DayOfWeek ( Date(3;9; y )) = 1; 9;
DayOfWeek ( Date(3;10; y )) = 1; 10;
DayOfWeek ( Date(3;11; y )) = 1; 11;
DayOfWeek ( Date(3;12; y )) = 1; 12;
DayOfWeek ( Date(3;13; y )) = 1; 13;
DayOfWeek ( Date(3;14; y )) = 1; 14;
);
dl1 = GetAsTimestamp(Date(3; day1; y) &" "& Time ( 2 ; 0 ; 0 ));     // if valid set timestamp at 2:00 AM

day2 = // current rules - daylight is always 1st Sunday in November
Case(
DayOfWeek ( Date(11;1; y )) = 1; 1;   // Test for each Sunday
DayOfWeek ( Date(11;2; y )) = 1; 2;
DayOfWeek ( Date(11;3; y )) = 1; 3;
DayOfWeek ( Date(11;4; y )) = 1; 4;
DayOfWeek ( Date(11;5; y )) = 1; 5;
DayOfWeek ( Date(11;6; y )) = 1; 6;
DayOfWeek ( Date(11;7; y )) = 1; 7;
);
dl2 = GetAsTimestamp(Date(11; day2; y) &" "& Time ( 2 ; 0 ; 0 ))   // if valid set timestamp at 2:00 AM
];


GetAsTimestamp(
endTS +
Case (
Min(begTS; endTS) <= dl1 and Max(begTS; endTS) >= dl1 and hours > 0; 
1;   // add in the spring
Min(begTS; endTS) <= dl1 and Max(begTS; endTS) >= dl1 and hours < 0; 
-1;   // add -1  in the spring if hours are negative
Min(begTS; endTS) <= dl2 and Max(begTS; endTS) >= dl2 and hours > 0;
-1;   // subtract in the fall
Min(begTS; endTS) <= dl2 and Max(begTS; endTS) >= dl2 and hours < 0;
1;   // add 1 in the fall if hours are negative
0)
* 3600 // hours  (number of seconds in a hour)
)
)

This is used in a medical situation where the number of hours between an event and a procedure is critical.

Take in consideration Day Light Savings Time if you need to calculate the correct time. This calculation only considers an overlap of 1 day light savings time.

You can enter hours as negative or positive

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

DayLightSavings ( TargetDate ; TargetTime ; Hours )

Comments

Support this website.

Support this website.