Time.org.uk

Suppose you have a date and time and want to know if it is within the BST or the GMT period.
The first thing to do is calculate when BST and GMT start for the year of your date.
The following two functions return the start date and time for BST and GMT respectively...

Public Function BSTstarts(ByVal TheYear As Integer) As String
  BSTstarts = "March " _
  & 31 - (Weekday("#March 31, " & TheYear & "#", 2) Mod 7) _
  & ", " & TheYear & " 01:00:00"
End Function

Public Function GMTstarts(ByVal TheYear As Integer) As String
  GMTstarts = "October " _
  & 31 - (Weekday("#October 31, " & TheYear & "#", 2) Mod 7) _
  & ", " & TheYear & " 01:00:00"
End Function

...each function uses the year to calculate the respective start date and time.

The functions make use of the fact that both BST and GMT start at 01:00 hours GMT on the last Sunday of a particular month.
GMTstarts(2024) will return the start date and time of GMT in the year 2024.
Now that you have a means of determining when BST and GMT start for a given year you can now calculate which period your date and time is within.

To do this you can call a function that returns TRUE or FALSE depending on the result...

Public Function IsGMT(ByVal TheDateAndTime As String) As Boolean
  IsGMT = True

  If DateDiff("s", TheDateAndTime, _
    BSTstarts(Year(TheDateAndTime))) < 1 Then IsGMT = False

    If DateDiff("s", TheDateAndTime, _
      GMTstarts(Year(TheDateAndTime))) < 1 Then IsGMT = True
  End Function

...this function uses the preceding two functions to calculate whether the start dates of BST and GMT have passed.

Each line of the function calculates the difference, in seconds, between your date and time and the start date and time of BST and GMT as returned by the functions BSTstarts and GMTstarts.
If the start date of BST has passed the result of the first calculation will be zero or negative.
The check for BST start is made first as this date occurs first in any year.
Now a check is made to see if the GMT start date has passed which will overide the previous result if necessary.
The returned value is 'True' if the date and time passed is within the GMT period, otherwise the result is 'False'.

At this point you have a means to determine which period a given date is within...

  If IsGMT("12 October 2024 15:30:00") Then
          ’It is within GMT
  Else
          ’It is within BST
  End If

...which will return 'False' indicating that "12 October 2024 15:30:00" is within the BST.

Now extend on the earlier code to determine which period is next and when the change will occur.

To determine the start date and time of each period we use the following two functions...

Public Function NextBST(ByVal TheDateAndTime As String) As String
  If DateDiff("s", TheDateAndTime, _
  BSTstarts(Year(TheDateAndTime))) < 1 Then
    NextBST = BSTstarts(Year(TheDateAndTime) + 1)
  Else
    NextBST = BSTstarts(Year(TheDateAndTime))
  End If
End Function

Public Function NextGMT(ByVal TheDateAndTime As String) As String
  If DateDiff("s", TheDateAndTime, _
  GMTstarts(Year(TheDateAndTime))) < 1 Then
    NextGMT = GMTstarts(Year(TheDateAndTime) + 1)
  Else
    NextGMT = GMTstarts(Year(TheDateAndTime))
  End If
End Function

...each function first determines if the date and time passed to it are beyond the start date and time of the respective period.

If your date and time is before the start date and time for your year then the result for your year is returned otherwise the result for the following year is returned.

Using the functions described so far we can calculate when the next start of the appropriate period is...

Public NextPeriod, StartDate, MyDateAndTime As String

MyDateAndTime = "12 October 2007 15:30:00"
’or
MyDateAndTime = Now()

’Avoid using Now() within your code as it is possible for it to
’cause paradoxical results if the computer time changes from one
’zone to another before all code has completed.

If IsGMT(MyDateAndTime) Then
  ’It is within GMT so we want the next BST start date
  NextPeriod = "BST"
  StartDate = NextBST(MyDateAndTime)
Else
  ’It is within BST so we want the next GMT start date
  NextPeriod = "GMT"
  StartDate = NextGMT(MyDateAndTime)
End If

...at this point you have now stored the name of the next period, and when it starts.

Using the code in these two columns you can now determine how long it is to the next clock change.