Time formula if great than 15

[Patriot2879]

Hi i hope you can help i would like a formula please. i have cell A1 which time stamps todays date and time when data is inputted in another cell. B1 has a 'yes' or 'no' in a list which gets selected. What i require is when A1 is updated with a time stamp and cell B1 = 'Yes' i want cell C1 to put in the time for when B1 was updated. Then in D1 i want a calculation to work out the time difference between cell A1 and C1 and if this is greater than 15 i want cell A1 to change to red colour. I hope this all makes sense and that you can help please?

 

[CountTepes]

That is an unusual solution and I can't see why it works.
However, like homeopathy. If it works for you, then there is no harm.

Glad I could help.

 

[Patriot2879]

thank you for all your help

 

[Patriot2879]

Hi Good afternoon, sorry just one more question, i was wondering is there anyway to count all the rows that go over the 15 minutes? for example to count the total that go red because it has gone over 15 minutes and to count the total into AD1 cell?

 

[CountTepes]

Here is a function from my library.
Put it into a module for it to work on any sheet. Use it like any worksheet function. type into a cell =CountColours(A1:A30,"Red"), and the cell will return the number of cells in the range which are red.
You can also call it from your code, in the same manner. But this time use it: T = CountColours(Range("A1:A30"), "Red")

Public Function CountColours(InRange As Range, InColour As Variant) As Variant
Application.Volatile
Dim C As Range
Dim Num As Long
Dim ColourVal As Long
Dim InColType As Variant


Select Case VarType(InColour)
    Case vbString ' A string
        Select Case UCase(InColour)
            Case "RED": ColourVal = vbRed
            Case "GREEN": ColourVal = vbGreen
            Case "BLUE": ColourVal = vbBlue
            Case "YELLOW": ColourVal = vbYellow
            Case "BLACK": ColourVal = vbBlack
            Case "WHITE": ColourVal = vbWhite
            Case "CYAN": ColourVal = vbCyan
            Case "MAGENTA": ColourVal = vbMagenta
            Case Else: InColour = "#VALUE!"
        End Select
    Case vbDouble, vbInteger, vbLong, vbSingle, vbDouble
        ColourVal = InColour
End Select
        
If InColour = "VALUE!" Then
    CountColours = InColour
  Else
    For Each C In InRange
        If C.Interior.Color = ColourVal Then Num = Num + 1
    Next
CountColours = Num
End If

 

[Patriot2879]

Here is a function from my library.
Put it into a module for it to work on any sheet. Use it like any worksheet function. type into a cell =CountColours(A1:A30,"Red"), and the cell will return the number of cells in the range which are red.
You can also call it from your code, in the same manner. But this time use it: T = CountColours(Range("A1:A30"), "Red")

Public Function CountColours(InRange As Range, InColour As Variant) As Variant
Application.Volatile
Dim C As Range
Dim Num As Long
Dim ColourVal As Long
Dim InColType As Variant


Select Case VarType(InColour)
    Case vbString ' A string
        Select Case UCase(InColour)
            Case "RED": ColourVal = vbRed
            Case "GREEN": ColourVal = vbGreen
            Case "BLUE": ColourVal = vbBlue
            Case "YELLOW": ColourVal = vbYellow
            Case "BLACK": ColourVal = vbBlack
            Case "WHITE": ColourVal = vbWhite
            Case "CYAN": ColourVal = vbCyan
            Case "MAGENTA": ColourVal = vbMagenta
            Case Else: InColour = "#VALUE!"
        End Select
    Case vbDouble, vbInteger, vbLong, vbSingle, vbDouble
        ColourVal = InColour
End Select
       
If InColour = "VALUE!" Then
    CountColours = InColour
  Else
    For Each C In InRange
        If C.Interior.Color = ColourVal Then Num = Num + 1
    Next
CountColours = Num
End If

Thank you for this I will try it out on Monday thankyou. Does it automatically update every time there Is a update?

 

[CountTepes]

Yes. The application.volatile statement, means that it will recalculate with the spreadsheet.

 

[Patriot2879]

Hi good morning, i have added the code into a module and then i added '= CountColours(Range("R5:R70"), "Red")' into an empty cell to count but the cell just says #Value, and hasnt counted the red cells in column R5 to R70, hope you can advise please?

Public Function CountColours(InRange As Range, InColour As Variant) As Variant
Application.Volatile
Dim C As Range
Dim Num As Long
Dim ColourVal As Long
Dim InColType As Variant


Select Case VarType(InColour)
    Case vbString ' A string
        Select Case UCase(InColour)
            Case "RED": ColourVal = vbRed
            Case Else: InColour = "#VALUE!"
        End Select
    Case vbDouble, vbInteger, vbLong, vbSingle, vbDouble
        ColourVal = InColour
End Select
        
If InColour = "VALUE!" Then
    CountColours = InColour
  Else
    For Each C In InRange
        If C.Interior.Color = ColourVal Then Num = Num + 1
    Next
CountColours = Num
End If

 

[CountTepes]

The formula should be treated as any other spreadsheet formula.

Start typing the formula, when you need the range, you can pick it up with the mouse, the same as is as if you did, =Sum(A1:A20), for example.

If you want to type it, don't have quote marks around the range.

= CountColours(R5:R70,"Red") ---------- When used from the spreadsheet

= CountColours(Range("R5:R70") ,"Red") ----- When called from a VBA subroutine.

 

[Patriot2879]

I changed that now thanks but now i get an error on the module on the 'End If' it says Compile error Expected End Function

 

[CountTepes]

Sorry. It looks like I missed the End Function statement at the end.