Chip Pearson informed us that from 2007 the US Spring DTS date change will
occur on the 2nd Sunday of March. Anyone interested in using the functions I
posted could replace 'LastSunday' with 'nThSunday' below.
The same function can be used to return the last Sunday of the month (ie 4th
or 5th Sunday) instead of the function 'LastSunday'.
Function nThSunday(yr As Long, mth As Long, nthSun As Long) As Date
Dim dt As Date
If nthSun < 1 Then
'eg, if nthSun = 0 return last Sunday of month
' so need to change mth to next month
mth = mth + 1
End If
dt = DateSerial(yr, mth, 0)
dt = dt + (nthSun * 7) + 1 - Weekday(dt)
nThSunday = dt
End Function
Sub test2()
Dim annus As Long
Dim dt2ndSunday As Date
Debug.Print "2nd Sunday in March"
For annus = 2007 To 2012
dt2ndSunday = nThSunday(annus, 3, 2)
Debug.Print Format(dt2ndSunday, "ddd dd-mmm-yyyy")
Next
Debug.Print "last Sunday in March"
For annus = 2007 To 2012
dt2ndSunday = nThSunday(annus, 3, 0)
Debug.Print Format(dt2ndSunday, "ddd dd-mmm-yyyy")
Next
End Sub
If necessary, to cater for the possibility of start time at say 1:00AM on
the
day of time change which actually changes at 2:00AM, in the function
change
d25 = LastSunday(Year(dStart), 10)
If d25 < dStart Then
'convert to next year if d25 is before dStart
d25 = LastSunday(Year(dStart) + 1, 10)
End If
to
d25 = LastSunday(Year(dStart), 10) + (2 / 24) ' ie 2:00AM
If d25 < dStart Then
'convert to next year if d25 is before dStart
d25 = LastSunday(Year(dStart) + 1, 10)
End If
And similarly change d23 earlier in the function to 2:00AM (perhaps also
replace 'LastSunday' with 'nThSunday'). As Chip also pointed out it would be
important that records reflect correct time in respect to DST time shift.
Regards,
Peter T
"Peter T" <peter_t@discussions wrote in message
...
This needs checking and double checking before deployment (any takers!).
The
idea is this -
There are 23 and 25 hours days in Spring & Autumn, which in the UK occur
on
the last Sundays of March and October.. Though I vaguely recall in the US
the Spring change is on the first Sunday of April ?
So if a 23 or 25 hour day occurs between start and end times it would be a
matter of subtracting or adding 1 hour to the difference in times. The
following is an initial attempt to do that:
Function DSTdiff(dStart As Date, dEnd As Date) As Date
Dim n As Long
Dim d25 As Date
Dim d23 As Date
If dStart dEnd Then
Exit Function
End If
''UK
' get last Sunday in March of year dStart, 23 hours
d23 = LastSunday(Year(dStart), 3)
If d23 <= dStart Then
'convert to next year if d23 is bfore dStart
d23 = LastSunday(Year(dStart) + 1, 3)
End If
''US ???
' ' get first Sunday in April of year dStart, 23 hours
' d23 = FirstSunday(Year(dStart), 4)
' If d23 <= dStart Then
' 'convert to next year if d23 is bfore dStart
' d23 = FirstSunday(Year(dStart) + 1, 4)
' End If
' get last Sunday in October of year dStart, 25 hours
d25 = LastSunday(Year(dStart), 10)
If d25 < dStart Then
'convert to next year if d25 is bfore dStart
d25 = LastSunday(Year(dStart) + 1, 10)
End If
'if d23 and/or d25 before dEnd then subtract 1 and/or add 1
If d23 < dEnd Then n = n - 1
If d25 < dEnd Then n = n + 1
If n Then
' convert to an hour
DSTdiff = n / 24
End If
End Function
Function LastSunday(yr As Long, mth As Long) As Date
Dim dt As Date
' last day of month
dt = DateSerial(yr, mth, 31)
If Weekday(dt) vbSunday Then
' convert to last Sunday
dt = dt - Weekday(dt) + 1
End If
LastSunday = dt
End Function
Function FirstSunday(yr As Long, mth As Long) As Date
Dim dt As Date
' last day of month
dt = DateSerial(yr, mth, 1)
If Weekday(dt) vbSunday Then
' convert to last Sunday of month
dt = dt + 8 - Weekday(dt)
End If
FirstSunday = dt
End Function
Sub test()
Dim d1 As Date, d2 As Date, hr As Date
' international users note these vb dates are US date format
'A1 straddle UK DST-1 (DST diff = -1)
d1 = #3/24/2006 8:00:00 AM#
d2 = #4/1/2006 8:00:00 PM#
hr = DSTdiff(d1, d2)
Debug.Print "A1", (d2 - d1) * 24, (d2 - d1 + hr) * 24
'' for US testing
'A2 straddle US DST-1 (DST diff = -1)
d1 = #3/31/2006 8:00:00 AM#
d2 = #4/7/2006 8:00:00 PM#
hr = DSTdiff(d1, d2)
Debug.Print "A2", (d2 - d1) * 24, (d2 - d1 + hr) * 24
'B straddle DST+1 (DST diff = +1)
d1 = #10/24/2006 8:00:00 AM#
d2 = #11/1/2006 8:00:00 PM#
hr = DSTdiff(d1, d2)
Debug.Print "B", (d2 - d1) * 24, (d2 - d1 + hr) * 24
'C straddle DST-1 and DST +1 over 18+ months (no DST diff)
d1 = #3/24/2007 8:00:00 AM#
d2 = #11/1/2007 8:00:00 PM#
hr = DSTdiff(d1, d2)
Debug.Print "C", (d2 - d1) * 24, (d2 - d1 + hr) * 24
'D straddle DST-1 and DST +1 with start & end in Nov (no DST diff)
d1 = #11/2/2006 8:00:00 AM#
d2 = #11/1/2007 8:00:00 PM#
hr = DSTdiff(d1, d2)
Debug.Print "D", (d2 - d1) * 24, (d2 - d1 + hr) * 24
End Sub
Regards,
Peter T
"Blue Knight" <Blue wrote in message
...
We use excel to track the number of hours / minutes from the placing of
a
order and when it was complete. It appears that excel does not take in
the
new US daylight saving time into affect when looking at elasped time or
time
range calculation.
Are there any patches being planned by microsoft specifically for excel
or
access or sql for use with the new daylight saving time? (So that from
Noon
on Saturday to Noon on Sunday will count as 23 and not 24 hours)