Compare dates

[jamasm2010]

Hi,
I have a worksheet that may contain up to 40K dates in a single column. Thedates will be in the following format, 4/5/2012 9:14.On another worksheet, a User will enter anywhere from 5 to 12 dates in a column in the following format, 4/6/2012. What is the best way to compare the 5 to 12 dates against the 40K dates using VBA? The purpose is to determine if data was enteredon day when the person was absent.
Thanks.
James

 

[isabelle]

hi James,

=SUMPRODUCT(--(YEAR(Sheet1!A1:A40000)=YEAR(A1))*(MONTH(Sheet1!A1:A40000)=MONTH(A1))*(DAY(Sheet1!A1:A40000)=DAY(A1)))


isabelle


Le 2012-12-26 12:56, (e-mail address removed) a écrit :

Hi,
I have a worksheet that may contain up to 40K dates in a single column.

The dates will be in the following format, 4/5/2012 9:14.On another
worksheet,

a User will enter anywhere from 5 to 12 dates in a column in the
following format, 4/6/2012.

What is the best way to compare the 5 to 12 dates against the 40K dates
using VBA?

The purpose is to determine if data was entered on day when the person
was absent.

 

[jamasm2010]

Hi,

I have a worksheet that may contain up to 40K dates in a single column. The dates will be in the following format, 4/5/2012 9:14.On another worksheet, a User will enter anywhere from 5 to 12 dates in a column in the following format, 4/6/2012. What is the best way to compare the 5 to 12 dates against the 40K dates using VBA? The purpose is to determine if data was entered on day when the person was absent.

Thanks.

James

Thanks, Isabelle. It worked like a charm!
James