Identify which dates are missing

[Kwnstantinos_M]

Hello,

I have two columns of dates as shown in the below table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]5/1/2016 01:00[/TD]
[TD]5/1/2016 01:00[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]5/1/2016 02:00[/TD]
[TD]5/1/2016 04:00[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]5/1/2016 02:00[/TD]
[TD]6/1/2016 05:00[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]5/1/2016 03:00[/TD]
[TD]7/2/2016 10:00[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]5/1/2016 04:00[/TD]
[TD]8/2/2016 05:00[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]5/1/2016 05:00[/TD]
[TD]9/3/2016 05:00[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]5/1/2016 06:00[/TD]
[TD]10/5/2016 07:00[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]5/1/2016 07:00[/TD]
[TD]12/5/2016 05:00[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]5/1/2016 08:00[/TD]
[TD]15/5/2016 18:00[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]5/1/2016 09:00[/TD]
[TD]18/6/2016 22:00[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]

The first column contain approximately 11,900 rows of dates and the second one 5900. I want to use a function in order to identify which exact dates and hours exist in both columns. As I have it in mind, the best way is to have a third column in which I will recieve a "yes" if it exists and "no" if it doesn't.. I used =MATCH(N2;$A$2:$A$5849;0) but the outcome wasn't right.. Do you have any ideas?

 

[James006]

Hello,

Why is your Match Function nor producing the right result ...???

Is it because of the times ( included in the dates ) not being identical ...

 

[Kwnstantinos_M]

Actually they are in the same format..what do you mean by saying identical?

 

[James006]

Well ... dates and times are numbers ...

Times are decimals ...

If your decimals are not exactly the same, Match will miss the target ...

Remember that Format is only a display ... it is not what is stored in the cell :wink:

 

[r1998]

Hello Kwnstantinos_M
data is from a1 to c10
[TABLE="width: 650"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]05-01-2016 01:00[/TD]
[TD]05-01-2016 01:00[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]05-01-2016 02:00[/TD]
[TD]05-01-2016 04:00[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]05-01-2016 02:00[/TD]
[TD]06-01-2016 05:00[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]05-01-2016 03:00[/TD]
[TD]07-02-2016 10:00[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]05-01-2016 04:00[/TD]
[TD]08-02-2016 05:00[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]05-01-2016 05:00[/TD]
[TD]09-03-2016 05:00[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]05-01-2016 06:00[/TD]
[TD]10-05-2016 07:00[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]05-01-2016 07:00[/TD]
[TD]12-05-2016 05:00[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]05-01-2016 08:00[/TD]
[TD]15-05-2016 18:00[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]05-01-2016 09:00[/TD]
[TD]18-06-2016 22:00[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]
in c1 enter this formula and drag down till c10

=IF(ISERROR(MATCH(B1,$A$1:$A$10,0)),"No","Yes")

 

[Kwnstantinos_M]

Thank you very much, works like charm!!

 

[r1998]

Thanks