VBA If And Or query on strings containing times

[NewOrderFac33]

Good afternoon,

I have a range containing a series of values in hh:mm:ss format.

If any of these durations exceed 10 minutes (the "10" being contained in a worksheet range ThresholdMinutes), I need to extract the minute section of the string and add it to a string variable MyString - this is no problem.

The issue I have is how to define the logic. It needs to be:

IF the minute portion of the string exceeds the value in ThresholdMinutes
OR
IF the minute portion of the value equals ThresholdMinutes AND the second portion of the value is greater then zero

I have the following:
If Mid(MyCell, 4, 2) > ThresholdMinutes Or Mid(MyCell, 4, 2) = ThresholdMinutes And Mid(MyCell, 7, 2) > 0)) Then
MyString= MyString ", " & Mid MyCell(4,2)
End If

but it doesn't work - the first line is highlighted red, due to my poor IF AND logic - can anyone help out, please?

Thanks in advance

Pete

 

[steve the fish]

What does Mid(MyCell, 4, 2) produce?

 

[NewOrderFac33]

Steve - that's a very good question. Not what I was expecting. For example, in my formula bar, the entry is 00:34:42, but the returned value is 10 (the actual value is 0.0241024), so whilst I can see the "10", I'm not sure why it's being returned.
I need to think again.
Pete

 

[Fluff]

What if you try

Mid(MyCell.[COLOR=#ff0000]Text[/COLOR], 4, 2)

 

[NewOrderFac33]

I ended up doing it like this:

                        If Minute(MyCell) > ThresholdMinutes Then
                            MyString = MyString & Minute(MyCell) & ", "
                        End If
                        
                        If Minute(MyCell) = ThresholdMinutes Then
                            If Second(MyCell) > 0 Then
                                MyString = MyString & Minute(MyCell) & ", "
                            End If
                        End If

Thanks for taking a look!

Pete

 

[Peter_SSs]

If any of these durations exceed 10 minutes (the "10" being contained in a worksheet range ThresholdMinutes), I need to extract the minute section of the string and add it to a string variable MyString - this is no problem.

The issue I have is how to define the logic. It needs to be:

IF the minute portion of the string exceeds the value in ThresholdMinutes
OR
IF the minute portion of the value equals ThresholdMinutes AND the second portion of the value is greater then zero

1. Are you saying that ThresholdMinutes is a named range on the worksheet?

2. You have the 10 in quote marks. Is that for emphasis or is the 10 stored in your sheet as as text value?

3. Does this mean that hours are irrelevant? So 03:07:02 does not exceed 10 minutes?

 

[NewOrderFac33]

Peter,
Yes, ThresholdMinutes is a named range containing the value 10.
Your point about hours is alarmingly spot on - I'll have to build in some more conditions - sometimes it takes another pair of eyes to spot the obvious! - Thank you!
Regards
Pete

 

[Peter_SSs]

Peter,
Yes, ThresholdMinutes is a named range containing the value 10.

Then I don't think you could have ended up using code like in post 5. Instead of

If Minute(MyCell) > ThresholdMinutes Then

you would have needed

If Minute(MyCell) > Range("ThresholdMinutes") Then

Your point about hours is alarmingly spot on - I'll have to build in some more conditions ..

Unless I haven't understood correctly, I think you should be able to check in one go. Something like ..

Sub Check_Minutes()
  Dim myString As String
  Dim myCell As Range
  Dim myThresholdMinutes As Date
  
  myThresholdMinutes = TimeValue("00:" & Range("ThresholdMinutes").Value & ":00")
  For Each myCell In Range("F1", Range("F" & Rows.Count).End(xlUp))
    If myCell.Value > myThresholdMinutes Then
      myString = myString & ", " & Format(myCell.Value, "nn")
    End If
  Next myCell
End Sub

 

[NewOrderFac33]

Hi, Peter,

I'd omitted the following from my post:
ThresholdMinutes = StatsSheet.Range("ThresholdMinutes")

The solution I'd come up with was:

                            MinuteValue = (Hour(MyCell) * 60) + Minute(MyCell)
                            
                            If MinuteValue > ThresholdMinutes Then
                                MyString = MyString & MinuteValue & ", "
                            End If
                            
                            If MinuteValue = ThresholdMinutes Then
                                If Second(MyCell) > 0 Then
                                    MyString = MyString & MinuteValue & ", "
                                End If
                            End If

but yours looks a little more elegant!

Thank you

Pete

 

[Peter_SSs]

Hi, Peter,

I'd omitted the following from my post:
ThresholdMinutes = StatsSheet.Range("ThresholdMinutes")

The problems with only showing code snippets & leaving helpers to guess.