Something I made that I thought some of you would find useful. Our organization uses Event Dates with Start and End times.
This solution will not display a different time depending on where the user of the report is located, however. I’ll leave that to someone else’s expertise to figure out. My particular report is viewed only by people in the Eastern Time Zone.
“But DST does not always land on the same date”, you ask? This was the tricky part. We need a way to determine when DST will be each year. The following two variables decide just that. They find the last day of the month, and then depending on what day of the week that is, subtracts the appropriate number of days to either get back to the Second Sunday of March, or the First Sunday of November. Since the number of days in both months is always the same, we can accomplish this. Thankfully, DST does not begin in February, with its variable length!
v_Daylight Begin Adj
=If(DayName(LastDayOfMonth([Event Start - Date]))=“Wednesday”) Then 17 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Thursday”) Then 18 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Friday”) Then 19 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Saturday”) Then 20 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Sunday”) Then 21 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Monday”) Then 22 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Tuesday”) Then 23
v_Daylight End Adj
=If(DayName(LastDayOfMonth([Event Start - Date]))=“Wednesday”) Then 24 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Thursday”) Then 25 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Friday”) Then 26 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Saturday”) Then 27 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Sunday”) Then 28 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Monday”) Then 29 Else If(DayName(LastDayOfMonth([Event Start - Date]))=“Tuesday”) Then 23
Then, once we know how to adjust the date, we need to further define when Daylight savings is. If month is not 3 or 11, then we can easily decide whether that date is in DST. For 3 and 11, calculations are done. The following variable will always return a Yes or a No value. You can then use this Yes/No value to determine how to modify a time field.
v_Daylight Savings
=If(MonthNumberOfYear([Event Start - Date])Between(4;10)) Then “Yes” ElseIf(MonthNumberOfYear([Event Start - Date]) InList(12;1;2))Then “No” Else If(MonthNumberOfYear([Event Start - Date])=3) Then If(DayNumberOfMonth([Event Start - Date])>=31-[v_Daylight Begin Adj];“Yes”;“No”) Else If(MonthNumberOfYear([Event Start - Date])=11) Then If(DayNumberOfMonth([Event Start - Date])<30-[v_Daylight End Adj];“Yes”;“No”)
The v_Start Time Variable is then able to adjust for DST. You will need to adjust the “-4” and “-5” values for your own time zone. The example shown is for Eastern Time Zone.
v_Start Time
=If([v_Daylight Savings]=“Yes”;ToDate(ToNumber(FormatDate([Event Start - Time];“HH”))-4;“HH”);ToDate(ToNumber(FormatDate([Event Start - Time];“HH”))-5;“HH”))
Duration of Event
The following variable will do a calculation to determine the number of hours for an event.
v_Duration
=If([Event Start - Date]=[Event End - Date]) Then ToNumber(FormatDate([v_Start Time];“HH”))-ToNumber(FormatDate([v_End Time];“HH”)) Else (24-ToNumber(FormatDate([v_Start Time];“HH”)))+ToNumber(FormatDate([v_End Time];“HH”))
If you should discover a problem with any of these as you check my work, please post comments here.
rdougherty 
(BOB member since 2008-10-06)