Getting wrong result because of the Cell's format!

[Mark00963]

Hi everyone,

I'm trying to get dates of "Check" Sheet into "Prod BILKON" sheet when I use the SUMPRODUCT function I get the result which's in the attached file, would you please help.

Thanks in advance.

[AliGW]

What result are you expecting? Why not just change the cell format?

[Mark00963]

I expect the two dates in Sheet "Check" Column "Print Go" - "Print Come"
I've tried all the possible Cell Formats but nothing changed, unfortunately.

[AliGW]

Try this:

=TEXT(SUMPRODUCT(((check!$B$2:$B$505)=$D2)*((check!$C$2:$C$505)=$E2),check!H$2:H$505),"dd/mm/yy")&"-"&TEXT(SUMPRODUCT(((check!$B$2:$B$505)=$D2)*((check!$C$2:$C$505)=$E2),check!I$2:I$505),"dd/mm/yyyy")

You will want to adapt it for when the SUMPRODUCT returns 0.

This maybe:

=IF(SUMPRODUCT(((check!$B$2:$B$505)=$D2)*((check!$C$2:$C$505)=$E2),check!H$2:H$505)=0,0,TEXT(SUMPRODUCT(((check!$B$2:$B$505)=$D2)*((check!$C$2:$C$505)=$E2),check!H$2:H$505),"dd/mm/yy"))&"-"&IF(SUMPRODUCT(((check!$B$2:$B$505)=$D2)*((check!$C$2:$C$505)=$E2),check!I$2:I$505)=0,0,TEXT(SUMPRODUCT(((check!$B$2:$B$505)=$D2)*((check!$C$2:$C$505)=$E2),check!I$2:I$505),"dd/mm/yyyy"))

[Mark00963]

Unfortunately, it doesn't work
I get the below returns:

43/mm/19-46/mm/2019
33/mm/19-44/mm/2019
42/mm/19-44/mm/2019
01/mm/19-43/mm/2019

[AliGW]

You will need to replace this: dd/mm/yyyy

with the Turkish equivalent - maybe this: dd.mm.yyyy

It's dependent on your machine's locale. When you get that right, it will work:

Excel 2016 (Windows) 32 bit

	K
1	PRINTING
2	0-0
3	0-0
4	0-0
5	0-0
6	0-0
7	0-0
8	29/04/19-03/05/2019
9	29/04/19-03/05/2019
10	09/05/19-14/05/2019
11	14/05/19-16/05/2019
12	19/04/19-20/04/2019

Sheet: PROD BILKON

[Mark00963]

Your way worked, thank you very much for your help,
only I have one more problem, on the second day, I have in the day field 46..48..etc.

[JeteMc]

Please upload a file that demonstrates the "have in the day field 46..48.." issue.
Let us know if you have any questions.