Conditionally formatting with if statement

Showhoff

New Member
Joined
Mar 1, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi, I hope to get some tips in how to solve a fairly easy problem:
My sheet has parameters with a time stamp to its value. I need to make averages of the parameters for every 10th minute. This is done by some ROUND and FLOOR functions and simply categorize each time interval with a number. First time interval is number 1, second time interval is 2 and so on. This works well. See table. Problem is when two parameters within the same columns has too much time between them which makes the categorization wrong for the paramerers. I highligted this with bold letters. The data sets are big, and the parameters are many.

Solution one: Is is possible to use conditionally formatting cells and highlight them if the diffrence between the lower cell and the upper neighbour cell is greater than ten minuted (as an example)?
Solution two: Use VBA and use a double for-loop to locate cells where the difference between the lower and the upper neihbour cell is greater than than ten minutes, and set in cells to ensure every time interval is included (the value of the cell put in is equal to the value of the cell above). This is out of my range as a coder in VBA.

25.07.2019 06:03:00​
06.00.00
31
42,9​
25.07.2019 05:47:28​
05.40.00​
29​
-1,3​
25.07.2019 06:10:39​
06.10.00​
32​
43​
25.07.2019 05:51:22​
05.50.00​
30​
-1,6​
25.07.2019 06:14:27​
06.10.00​
32​
43,1​
25.07.2019 05:55:10​
05.50.00​
30​
-2,1​
25.07.2019 06:18:20​
06.10.00​
32​
42,9​
25.07.2019 05:59:00
05.50.00
30​
-2,2​
25.07.2019 06:22:11​
06.20.00​
33​
43​
25.07.2019 06:18:14
06.10.00
31
-2​
25.07.2019 06:26:05​
06.20.00​
33​
43,1​
25.07.2019 06:22:05​
06.20.00​
32​
-1,8​
25.07.2019 06:29:58​
06.20.00​
33​
43​
25.07.2019 06:29:51​
06.20.00​
32​
-2​

Thanks, grateful for every help.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,297
Office Version
  1. 365
Platform
  1. Windows
With CF you can use

+Fluff.xlsm
ABCD
1Date timeTime
225.07.2019 05:47:2805:40:0029-1,3
325.07.2019 05:51:2205:50:0030-1,6
425.07.2019 05:55:1005:50:0030-2,1
525.07.2019 05:59:0005:50:0030-2,2
625.07.2019 06:18:1406:10:0031-2
725.07.2019 06:22:0506:20:0032-1,8
825.07.2019 06:29:5106:20:0032-2
List
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:D8Expression=$B2>$B1+1/144textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,114,339
Messages
5,547,360
Members
410,789
Latest member
cpolek83
Top