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

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, 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.
Thanks.
James

On Wednesday, December 26, 2012 11:56:01 AM UTC-6, wrote:
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