Flagging a Column if timestamp occurs within a specified timestamp of another column.

Hi,

Check out the conditional formatting that I have used in Column D in the attachment.

Hope this helps

This is good except for one issue that I should have specified before.

There could be 500 rows of Column A and B and only 100 rows of column C and D. The flagging of column D needs to be done if the timestamp occurs anywhere in the column of B (with column A date taken into account)

Sure. Thanks again I really do appreciate the help.

To step back. Basically what I'm trying to do is multi-channel attribution of television commercial to an actual lead submitted on the website. Column A+B is the time the television commercial ran. Column b+c is the time the lead was submitted on the website.

The 30 minute flag is the time i'm allowing for the attribution to count to the television commercial

data-sample.xlsx

Originally Posted by newdoverman

The first time in your example yields a negative time .... not possible so I changed it to something reasonable.

Thinking that there was something wrong with your data, I copied it over into my own workbook. There was only 1 time that was 30 minutes or less. I changed another time so that the duration was less than 30 minutes and it appears to work.

Play around with the uploaded file to see if it meets your needs.

Thanks. unfortunately this doesn't work. The reason is that the formula is only flagging if it's within 30 minutes in the same row. The website lead could have been generated by any time stamp in Column A+B

i.e.

Column C+D Row 2 has a time stamp of 2/14/2014 13:05

Column A+B Row 2 is 2/05/2014 12:15 (which wouldn't flag it) but Row 95 could be 2/14/2014 12:55 which should cause the time stamp in row 2 to be flagged.

My thought is that the formula would check if any timestamp in A+B fit the criteria for Column D Row 2 to be flagged (etc for all rows in column D)

Hi,

Try this instead of my previous attachment. I have inserted data into column E (hidden) which returns whether or not there is a time anywhere from columns A&B that is within 30 minutes of the time on that row in columns C&D. I have then used conditional formatting to highlight any rows that contain a TRUE value in column E.

I hope this helps

Sorry, my bad. Try this attachment instead...I forgot to use an array formula in column E

Sorry to spam attachments at you :P, but here's the same thing without the helper column. The formula that was in column E is now wholly contained within the conditional formatting formula

Originally Posted by ajryan88

Sorry to spam attachments at you :P, but here's the same thing without the helper column. The formula that was in column E is now wholly contained within the conditional formatting formula

Hi Ajryan88,

This last attachment is really good. One small problem though is that it is highlighting rows that happen 30 minutes before as well as 30 minutes afterwards.

Is there an easy fix to make it only highlight if it's 30 minutes after column A+B and not before the A+B time stamp?

Thanks,
Joe Butch

Ah sorry, I didn't realise it was only supposed to flag 30 minutes after. Check the new attachment

Changed the conditional format formula from

Formula:

Please Login or Register  to view this content.

to

Formula:

Please Login or Register  to view this content.

Hi ajryan88

Using your code I found an error with the conditional formatting. I've attached an excel sheet that shows it.

The attached spreadsheet should only highlight row 2 which falls in the criteria of up to 30 minutes after column A+B.

For some reason it's highlighting row 3.

data-sample-4.xlsx

Hi,

It is because you changed the range of the conditional formatting to $C:$D. This means that the relative references in the formula won't work, as they were based on the range starting in row 2 (hence why rows 1 and 3 are highlighted instead of rows 2 and 4). The solution is to change the range to something like $C2:$D10000, where 10000 is a number big enough to ensure that all of your data is captured by the formula.

Ah.. Yes! Thanks for the help.

No problems.

Please don't forget to mark this thread as solved, and please click on the * next to my post to say thanks

Have a great day