SUMPRODUCT HELP

[ryan.fitzpatrick3]

I have a sumproduct equation which I've used in the past and it works
great, but I've applied it to another application and now I it's
having trouble with matching dates. I pull information from a access
table onto a worksheet. the date (transaction date) reflects as
01/01/2008 02:23.01 PM. I have another sheet that has everyday of the
a month on it, so one sheet would be all of january, sheet 2 would be
Feb, sheet 3 Mar, etc. all with dates on it. The sumproduct equation
would match the 5 criteria (which works already except for matching up
dates) and put the correct volume with the correct day in the month
sheets.

As the headers of the months I have the dates listed as 01/01/008,
01/02/2008.....1/31/2008 with no time. The sumproduct seems to be
stuck on matching the date formats up. I did isnumber on both and both
are numbers.


=SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Period1!$C5), 'this
matches store #'
--(1*'Pulled from Access'!$C$7:$C$5420=Period1!
$D5), 'this matches item #'
--(1*'Pulled from Access'!$P$7:$P$5420=Period1!
C8), 'this matches date'
--('Pulled from Access'!$I$7:$I
$5420="R"), 'this matches received
voucher'
'Pulled from Access'!$G$7:$G
$5420) 'this pulls
correct volume'


Basically this equation states that the volume of the correct, item,
plant and date that meets the certain voucher number will get placed
in the the correct date in the month.

Can anyone help?

Ryan

 

[Rick Rothstein \(MVP - VB\)]

Actually, in looking more closely at what you have, I would think your
multiply by 1 should be converting the text date into a real date for your
equality tests. I guess the next question is whether your "dates" on the
Period1 sheet are really dates or are they text? If text, multiplying them
by 1 would convert them to real dates for the comparison; if not text, then
do they have time portions on them (you are doing a full equality check, so
the date and time portions would have to match in order for there to be
equality. Perhaps wrapping the "dates" from access in an INT function to
remove the time part would work. If your SUMPRODUCT range covers blank cells
(in anticipation of future filling in), then you will need to add a test to
the SUMPRODUCT requiring the range not be blank.

Rick

 

[ryan.fitzpatrick3]

Rick,

Thank you. I found out it's the date that is transferred from Access.
Excel doesn't recognize the time stamp at the end of the date as a
match, although isnumber and exact said they were the same. Once I
manually deleted the time stamp out of the date, the sumproduct code
worked, so I'm 100% confident it has to do with the access date.

Wrapping the dates with int function how do I go about doing that.
I've been trying to use format, datevalue and had no luck getting rid
of that time stamp on the transfer. Could you tell me how to do that?

 

[ryan.fitzpatrick3]

Rick,

I figured it out. I had to do it on the Access side. Thank you for all
your help.

ryan