Daylight Saving Time (DST) and calculating time difference.
 

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)

Daylight Saving Time (DST) and calculating time difference.
 

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)

Daylight Saving Time (DST) and calculating time difference.
 

See http://www.cpearson.com/excel/DaylightSavings.htm for a discussion
about the change in DST dates for 2007 and beyond, as well as a number of
functions for working with DST and Standard Time.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Peter T" <peter_t@discussions wrote in message
...
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)

Daylight Saving Time (DST) and calculating time difference.
 

I should have looked at your link before, I hadn't.

However I think the relatively simple examples I posted work reasonably
well, albeit with caveats as regards correctly recorded times, computer
clock correctly set, and no ambiguity of start/end times between 1 to 3 AM
on day of changeover.

Regards,
Peter T

"Chip Pearson" wrote in message
...
See http://www.cpearson.com/excel/DaylightSavings.htm for a discussion
about the change in DST dates for 2007 and beyond, as well as a number of
functions for working with DST and Standard Time.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Peter T" <peter_t@discussions wrote in message
...
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)