Is it time?

[sts023]

Hi guys - this twit's benused again!
I'm trying to write a function to return a string of comma delimited times in the format "hh:mm" representing 24 hours from a given start time with a specified interval. (Please dont ask why - life's too short!)
To add to the fun, I also want to include a start and end time inside which I don't want the values generating.
So I could generate for example every 20 minutes between 14:00 on one day to 14:00 on the next day, but not including times after 22:00 today and before 08:00 tomorrow.
The test harness and code follow:0
TEST HARNESS

Sub Times()
Dim intMins             As Integer
Dim strResult           As String
Dim strStart            As String
Dim strPauseAfter       As String
Dim strPauseUntil       As String
  strStart = "14:00"
  strPauseAfter = "22:00"
  strPauseUntil = "08:00"
  intMins = 30
  strResult = funIntervals(strStart, _
                           strPauseAfter, _
                           strPauseUntil, _
                           intMins)
  MsgBox "Start Time " & strStart & vbCrLf & _
         "Pause after " & strPauseAfter & " until " & strPauseUntil & vbCrLf & _
         str
End Sub

CODE

Public Function funIntervals(strStart As String, _
                             strPauseAfter As String, _
                             strPauseUntil As String, _
                    Optional intMins = 15) As String
'*
'** Generate a string of comma delimited
'** time values in intMins minute intervals
'** between strStart time and strStart
'** time plus 1 day, but not including
'** times between strPauseAfter and
'** strPauseUntil.
'*
Dim booRecord               As Boolean
Dim datPauseAfter           As Date
Dim datStart                As Date
Dim datStop                 As Date
Dim datThisQH               As Date
Dim datTime                 As Date
Dim datPauseUntil                As Date
Dim intPtr                  As Integer
Dim strResult               As String




  datStart = Date + TimeValue(CDate(strStart & ":00"))
  datStop = DateAdd("d", 1, datStart)
  
  datPauseAfter = Date + TimeValue(CDate(strPauseAfter & ":00"))
  datPauseUntil = Date + TimeValue(CDate(strPauseUntil & ":00"))
  datPauseUntil = DateAdd("d", 1, datPauseUntil)
  
  datThisQH = datStart
  
  strResult = ""
'*
'** Outer loop
'** First Start to last End.
'*
  Do Until datThisQH > datStop
    booRecord = True
    If datThisQH > datPauseAfter Then
      If datThisQH < datPauseUntil Then
        booRecord = False
      End If
    End If
    If booRecord Then
      datTime = TimeValue(datThisQH)
      strResult = strResult & _
                  "," & _
                  Left(CStr(datTime), 5)
    End If
    datThisQH = datThisQH + CDate("00:" & CStr(intMins) & ":00")
  Loop
  funIntervals = Right(strResult, Len(strResult) - 1)
End Function 'FunIntervals

The problem comes in the line

    If datThisQH > datPauseAfter Then

(btw - it's called datThisQH 'cos originally it was going to be used to generate Quarter Hour intervals).
Try running the code with a break on the line after the one giving the problem.
The values seem to be identical on a Watch (datThisQH and DatPauseAfter), but the code says datThisQH is greate than datPauseAfter!
Any kind soul got any bright ideas as to why?
Please???

 

[KolGuyXcel]

I'm not getting this error!

Instead, I'm getting an error in Sub Times()!

I guess, the last line which is showing "str" should be "strResult".

Once I modify that the macro's working fine and populating the message box with 30 min intervals, separated by comma!

 

[Fluff]

Add this line

    If datThisQH > datPauseAfter Then
[COLOR=#ff0000]Debug.Print datThisQH - datPauseAfter[/COLOR]

Then set a break on the next & have a look at the immediate window.

 

[sts023]

Sorry KolGuyExcel – that was a cut-n-paste mismatch. It should have read “strResult”.

Fluff – I stuck the following code in to see what Excel thought the values were during each iteration.

    Debug.Print "QH: " & datThisQH & _
                ", PA: " & datPauseAfter & _
                ", Dif: " & (datThisQH - datPauseAfter)

At the point where I expected equality (22:00) I got

QH: 06/11/2018 22:00:00, PA: 06/11/2018 22:00:00, Dif: 4.36557456851006E-11

Although I’ve no experience with E numbers, this implies that there’s a small difference in the datetime values.
Is there a simple way of getting around this, or do I have to get laborious by testing the Year/Month/Day/Hour/Min/Sec of each variable separately?
All ideas gratefully received.

 

[Fluff]

try

    If Round(datThisQH, 10) > Round(datPauseAfter, 10) Then

 

[sts023]

Thanks Fluff - works like a dream!
The only problem is I don't know WHY it works. What do the first 10 digits of a date variable (or "IEEE 64-bit (8-byte) floating-point number") represent?
I could ASSUME "yyyymmddhhmm", but that may just make an *** of U and ME (sorry - Oldies but Goldies).

 

[Fluff]

I suspect it's got something to do with Floating point arithmetic, but as I've no idea what that is/or means, I can't help.

 

[MARK858]

Thanks Fluff - works like a dream!
The only problem is I don't know WHY it works.

See the link below. In particular from the section on precision.

https://support.microsoft.com/en-gb...ithmetic-may-give-inaccurate-results-in-excel

 

[sts023]

Thanks Mark858. I'm sure this reference is helpful, but although I took Maths and Further Maths at A level in the '60s (19, not 18!), some (most?) of the grey cells have decayed, and I'm turning into one of those irritating "why dunt it work like wot I want it to?" abominations.

For the moment I'll try to remember that comparing datetime variables may be inaccurate unless rounding is involved, and store this code snippet in my MZ-Tools library of "not exactly sure why, but do it like this" examples.

Thanks again to all involved!

 

[MARK858]

I'll try to remember that comparing datetime variables

Just remember that it isn't just datetime it applies to, the rules are the same for all numbers.