Time range between specific value

[sanrama]

Hi,

I want to find time range between specific rang of value
Please refer below table

Downtime - Chlorine flow.xlsx
	B	C	D	E	F
2		Time	01-Jan	02-Jan	03-Jan
3		0:00	1939	0	1500
4		1:00	1946	0	0
5		2:00	1946	150	0
6		3:00	1945	1200	0
7		4:00	1951	1964	0
8		5:00	1954	1966	1839
9		6:00	1943	1942	1840
10		7:00	1936	1939	1839
11		8:00	1935	0	1838
12		9:00	1943	0	0
13		10:00	1948	0	0
14		11:00	1940	0	0
15		12:00	1500	0	1844
16		13:00	1000	250	1847
17		14:00	200	280	1851
18		15:00	100	1946	1849
19		16:00	0	1950	1841
20		17:00	0	1942	0
21		18:00	0	1941	0
22		19:00	0	1958	0
23		20:00	0	700	1836
24		21:00	200	200	1835
25		22:00	400	0	1840
26		23:00	1939	0	1845
27
28	Required Value
29
30	Time at 1st Instance when value goes below 300	From	14:00	0:00	1:00
31	Time at 1st Instance when value goes ablove 300	To	21:00	2:00	4:00
32
33	Time at 2nd Instance when value goes below 300	From		8:00	9:00
34	Time at 2ndInstance when value goes ablove 300	To		14:00	11:00
35
36	Time at 3rd Instance when value goes below 300	From		20:00	17:00
37	Time at 3rd Instance when value goes ablove 300	To		23:00	19:00
38
39	Time at 4th Instance when value goes below 300	From
40	Time at 4th Instance when value goes ablove 300	To
December

Cell Formulas
Range	Formula
C3	C3	=C26+1/24
C10:C26,C4:C8	C4	=C3+1/24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:F26	Cell Value	<900	text	NO

 

[shknbk2]

If you are open to a user-defined function (UDF), this code was successful for me. Put this code into a VBA Module.

Function GetTime(Target As Range, Instance As Integer, IsBelow As Boolean)
    Dim HourCells As Range
    Dim HourCell As Range
    Dim iInstance As Integer
    Dim i As Integer
    Dim j As Integer
    Dim Below As Range
    Dim Above As Range
    
    iInstance = 1
    Set HourCells = Range(Cells(3, Target.Column), Cells(26, Target.Column))
    Do
        Set Below = Nothing
        Set Above = Nothing
        For i = iInstance To 24
            If CInt(HourCells(i)) <= 300 Then
                Set Below = HourCells(i)
                If i = 24 Then
                    If CInt(HourCells(1).Offset(0, 1)) >= 300 Then
                        Set Above = HourCells(24)
                    End If
                    Instance = Instance - 1
                    Exit Do
                Else
                    For j = i + 1 To 24
                        If CInt(HourCells(j)) >= 300 Then
                            Set Above = HourCells(j - 1)
                            iInstance = j
                            Exit For
                        End If
                    Next j
                    If j = 25 Then
                        If CInt(HourCells(1).Offset(0, 1)) >= 300 Then
                            Set Above = HourCells(24)
                        End If
                        Instance = Instance - 1
                        Exit Do
                    End If
                End If
                Exit For
            End If
        Next
        Instance = Instance - 1
    Loop While Instance > 0
    GetTime = ""
    If Instance = 0 Then
        If IsBelow Then
            If Not Below Is Nothing Then
                GetTime = Cells(Below.Row, 3).Value
            End If
        Else
            If Not Above Is Nothing Then
                GetTime = Cells(Above.Row, 3).Value
            End If
        End If
    End If
End Function

If you want the sheet to automatically update the UDFs, you can put this into the Sheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.CalculateFull
End Sub

GetTime.xlsm
	B	C	D	E	F
2		Time	01-Jan	02-Jan	03-Jan
3		0:00	1939	0	1500
4		1:00	1946	0	0
5		2:00	1946	150	0
6		3:00	1945	1200	0
7		4:00	1951	1964	0
8		5:00	1954	1966	1839
9		6:00	1943	1942	1840
10		7:00	1936	1939	1839
11		8:00	1935	0	1838
12		9:00	1943	0	0
13		10:00	1948	0	0
14		11:00	1940	0	0
15		12:00	1500	0	1844
16		13:00	1000	250	1847
17		14:00	200	280	1851
18		15:00	100	1946	1849
19		16:00	0	1950	1841
20		17:00	0	1942	0
21		18:00	0	1941	0
22		19:00	0	1958	0
23		20:00	0	700	1836
24		21:00	200	200	1835
25		22:00	400	0	1840
26		23:00	1948	0	1845
27
28	Required Value
29
30	Time at 1st Instance when value goes below 300	From	14:00	0:00	1:00
31	Time at 1st Instance when value goes ablove 300	To	21:00	2:00	4:00
32
33	Time at 2nd Instance when value goes below 300	From		8:00	9:00
34	Time at 2ndInstance when value goes ablove 300	To		14:00	11:00
35
36	Time at 3rd Instance when value goes below 300	From		21:00	17:00
37	Time at 3rd Instance when value goes ablove 300	To		23:00	19:00
38
39	Time at 4th Instance when value goes below 300	From
40	Time at 4th Instance when value goes ablove 300	To
Sheet1 (2)

Cell Formulas
Range	Formula
C4:C26	C4	=C3+1/24
D30:F30	D30	=GetTime(D30,1,TRUE)
D31:F31	D31	=GetTime(D31,1,FALSE)
D33:F33	D33	=GetTime(D33,2,TRUE)
D34:F34	D34	=GetTime(D34,2,FALSE)
D36:F36	D36	=GetTime(D36,3,TRUE)
D37:F37	D37	=GetTime(D37,3,FALSE)
D39:F39	D39	=GetTime(D39,4,TRUE)
D40:F40	D40	=GetTime(D40,4,FALSE)

 

[sanrama]

Thanks shknbk2
I need to use excel formula instead of VBA.

Can we solve by excel formula ?

 

[shknbk2]

Possibly, but someone else will have to provide it.

 

[sanrama]

Thanks shknbk2