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)