Conditional Formatting, Dates and Times

[emfish]

Hey guys,

** Context **
I work in television advertising, where we have to make sure that none of our spots (ie. the times when our ads are showing) are playing on the same station (channel) within a 16 minute interval.

Each week, we copy all of the spots (often tens of thousands) that we have into an excel document.

The data look something like the following (but imagine 10000000000 more rows):

** Brief **

I am trying to find a quick and easy way to not only identify, but also single out the, what we call, "consecutive breaks" - i.e. spots that play on the same station within a 16 minute interval.

At the moment, I have done the following:

• As the time is pasted in as a number, I am unable to simply reformat it into a time, so I have added a column to the left with the following formula : =TIMEVALUE(LEFT(F2,LEN(F2)-2)&":"&RIGHT(F2,2))
  This has given the data in column F a time function.
  
• I then applied Conditional Formatting to the cells with column A the following custom formula: =ABS(A2-A1)<16/(24*60)
  As you can see, this has made the spots appearing within a 16 minute interval appear in Red.
  HOWEVER, this doesn't take into account the date, which is why the data in cell a5 has also appeared in red.
  Additionally, this only highlights ONE of the rows involved in the consecutive break (e.g. A2, but not A3), which means I cannot simply filter by "Consecutive breaks", as one of them would not appear.
  
• If the date was able to be taken into consideration in the conditional formatting, I could leave it as is, and scroll through all of the ones that appear in Red, and pair them up, however, when there are huge amounts of data, this takes FOREVER, and I'm sure there is a more efficient way of doing this.

My goal is to make all of the spots that appear on the same date, within a 16 minute interval appear in red, so that I can then set a "filter by colour" filter, and see them all there.

Thank you in advance for your help,

Emma

 

[etaf]

i'm assuming you are sorting in date and time order
=ABS(A2-A1)<16/(24*60)
Add an AND to also include the date

=AND( ABS(A2-A1)<16/(24*60) , E2=E1 )
however ,
if the ad goes over midnight and say runs at 23:55 and again at 00:05
is that to be included and counted ?

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[emfish]

Hi Etaf,

Thank you so much for your response.

Unfortunately, I'm struggling to download the software onto my work laptop.. however, please see if the following link works for you:

https://docs.google.com/spreadsheet...ouid=116862349695957173846&rtpof=true&sd=true

Thanks for your help,

Emma

 

[etaf]

you need to grant access for anyone to open

 

[emfish]

Ah apologies, please try this one ?

Clash Report.xlsx

 

[etaf]

formula works ok for me , need to change the range to meet your data , B and G
also added B2<>"" , so blanks are not included

how is this , but as i say

if the ad goes over midnight and say runs at 23:55 and again at 00:05
is that to be included and counted ?

XL2BB below and on dropbox - dropbox will only be on for a few days, then i delete the file

Dropbox - File Deleted - Simplify your life

www.dropbox.com

as you have the time elapsed in column C , you could use that maybe something like =AND(C2<>"",C2<=TIMEVALUE("0:15:0")) - BUT you have -ve time

Clash Report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Station	Client Short Name	Day	Date	Time	Sec	Product Short Name	Programme Before
2		11:40:00		CARLTON	Brand 1	MON	5/16/22	1140	20	Product 1	THIS MORNING		FALSE
3		11:55:00	0:15:00	CARLTON	Brand 1	MON	5/16/22	1155	30	Product 2	LOOSE WOMEN		TRUE
4		13:28:00	1:33:00	CARLTON	Brand 1	MON	5/16/22	1328	30	Product 3	LOOSE WOMEN		FALSE
5		16:15:00	2:47:00	CARLTON	Brand 1	MON	5/16/22	1615	10	Product 4	TIPPING POINT		FALSE
6		16:30:00	0:15:00	CARLTON	Brand 2	MON	5/16/22	1630	10	Product 1	TIPPING POINT		TRUE
7		16:58:00	0:28:00	CARLTON	Brand 1	MON	5/16/22	1658	30	Product 2	TIPPING POINT		FALSE
8		19:58:00	3:00:00	CARLTON	Brand 1	MON	5/16/22	1958	20	Product 3	EMMERDALE		FALSE
9		10:15:00	###############################################	CARLTON	Brand 1	TUE	5/17/22	1015	10	Product 4	THIS MORNING		FALSE
10		02:25:00	###############################################	CARLTON	Brand 2	TUE	5/17/22	2625	10	Product 1	THIS MORNING		FALSE
11		14:15:00	11:50:00	CARLTON	Brand 1	TUE	5/17/22	1415	30	Product 2	DICKINSONS REAL DEAL REPEAT		FALSE
12		14:30:00	0:15:00	CARLTON	Brand 1	TUE	5/17/22	1430	20	Product 3	TENABLE RPT		TRUE
13		16:15:00	1:45:00	CARLTON	Brand 1	TUE	5/17/22	1615	10	Product 4	TIPPING POINT		FALSE
14		17:30:00	1:15:00	CARLTON	Brand 1	TUE	5/17/22	1730	10	Product 1	LINGO RPT		FALSE
15		20:15:00	2:45:00	CARLTON	Brand 2	TUE	5/17/22	2015	10	Product 2	MARTIN CLUNES ISLANDS OF OZ RP		FALSE
16		10:50:00	###############################################	CARLTON	Brand 1	WED	5/18/22	1050	30	Product 3	THIS MORNING		FALSE
17		11:25:00	0:35:00	CARLTON	Brand 1	WED	5/18/22	1125	30	Product 4	THIS MORNING		FALSE
18													FALSE
19													FALSE
20													FALSE
21													FALSE
22													FALSE
Sheet1

Cell Formulas
Range	Formula
B2:B17	B2	=TIMEVALUE(LEFT(H2,LEN(H2)-2)&":"&RIGHT(H2,2))
C3:C17	C3	=B3-B2
M2:M22	M2	=AND( ABS(B1-B2)<16/(24*60), G2=G1,B2<>"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C34	Expression	=AND( ABS(B1-B2)<16/(24*60), G2=G1,B2<>"")	text	NO

 

[etaf]

so for example using column C - see formula in column N
also fixed the -ve times

Clash Report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Station	Client Short Name	Day	Date	Time	Sec	Product Short Name	Programme Before		Date&Time	Just C
2		11:40:00		CARLTON	Brand 1	MON	5/16/22	1140	20	Product 1	THIS MORNING		FALSE	FALSE
3		11:55:00	0:15:00	CARLTON	Brand 1	MON	5/16/22	1155	30	Product 2	LOOSE WOMEN		TRUE	TRUE
4		13:28:00	1:33:00	CARLTON	Brand 1	MON	5/16/22	1328	30	Product 3	LOOSE WOMEN		FALSE	FALSE
5		16:15:00	2:47:00	CARLTON	Brand 1	MON	5/16/22	1615	10	Product 4	TIPPING POINT		FALSE	FALSE
6		16:30:00	0:15:00	CARLTON	Brand 2	MON	5/16/22	1630	10	Product 1	TIPPING POINT		TRUE	TRUE
7		16:58:00	0:28:00	CARLTON	Brand 1	MON	5/16/22	1658	30	Product 2	TIPPING POINT		FALSE	FALSE
8		19:58:00	3:00:00	CARLTON	Brand 1	MON	5/16/22	1958	20	Product 3	EMMERDALE		FALSE	FALSE
9		10:15:00		CARLTON	Brand 1	TUE	5/17/22	1015	10	Product 4	THIS MORNING		FALSE	FALSE
10		02:25:00		CARLTON	Brand 2	TUE	5/17/22	2625	10	Product 1	THIS MORNING		FALSE	FALSE
11		14:15:00	11:50:00	CARLTON	Brand 1	TUE	5/17/22	1415	30	Product 2	DICKINSONS REAL DEAL REPEAT		FALSE	FALSE
12		14:30:00	0:15:00	CARLTON	Brand 1	TUE	5/17/22	1430	20	Product 3	TENABLE RPT		TRUE	TRUE
13		16:15:00	1:45:00	CARLTON	Brand 1	TUE	5/17/22	1615	10	Product 4	TIPPING POINT		FALSE	FALSE
14		17:30:00	1:15:00	CARLTON	Brand 1	TUE	5/17/22	1730	10	Product 1	LINGO RPT		FALSE	FALSE
15		20:15:00	2:45:00	CARLTON	Brand 2	TUE	5/17/22	2015	10	Product 2	MARTIN CLUNES ISLANDS OF OZ RP		FALSE	FALSE
16		10:50:00		CARLTON	Brand 1	WED	5/18/22	1050	30	Product 3	THIS MORNING		FALSE	FALSE
17		11:25:00	0:35:00	CARLTON	Brand 1	WED	5/18/22	1125	30	Product 4	THIS MORNING		FALSE	FALSE
18													FALSE	FALSE
19													FALSE	FALSE
20													FALSE	FALSE
21													FALSE	FALSE
22													FALSE	FALSE
23													FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
B2:B17	B2	=TIMEVALUE(LEFT(H2,LEN(H2)-2)&":"&RIGHT(H2,2))
M2:M23	M2	=AND( ABS(B1-B2)<16/(24*60), G2=G1,B2<>"")
N2:N23	N2	=AND(C2<>"",C2<=TIMEVALUE("0:15:0"))
C3:C17	C3	=IF(B3-B2<0,"",B3-B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C34	Expression	=AND( ABS(B1-B2)<16/(24*60), G2=G1,B2<>"")	text	NO