Hi,
I want to find time range between specific rang of value
Please refer below table
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 |