my formula isnt working because of date formatting

[bigdan]

I'm trying to use a sumproduct formula but it's not working because of date formatting. I know it's date related because I was able to make it work on made up dates.

So if I was to type in smth like "7/5" without the quotes Excel would read that as July 5 2019 and display it as "05-Jul". If I were to put my cursor on that the address bar would say "07/05/2019".

The problem with the source data is it's displayed as "2019-07-05".

I would've thought that wouldn't be a big deal but apparently it is. The **** formula isn't working. Can anyone help?

FYI I'm using sumproduct to find the results within a certain range of dates. For this example let's say I need dates within Jul 1 and Jul 7.

 

[fhqwgads]

formula isn't working how?
is it reading your source date 2019-07-05 as may 7th but you want jul 5th?
or is it giving you a #VAL error or something because the source date is being read as text instead of a number?
can you post your formula?

 

[Sam_D_Ben]

I am not sure if this work for your case.

Excel 2013/2016
	A	B
1	7/5/2019	1
2
3	05-Jul-2019
Sheet1

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--(A1=DATE(2019,7,5)))
A3	=TEXT(DATE(2019,7,5),"DD-MMM-YYYY")

 

[bigdan]

Correct, I get a #VALUE ! error. Here's the formula. Sorry it's so ugly!

=SUMPRODUCT(--(AB1:AB139478="Completed")*(V1:V139478=W139491)*(B1:B139478>=U139493)*(B1:B139478<=V139493)*AN1:AN139478)

I tried this formula on an experimental range with dates I made up and it worked just fine.

formula isn't working how?
is it reading your source date 2019-07-05 as may 7th but you want jul 5th?
or is it giving you a #VAL error or something because the source date is being read as text instead of a number?
can you post your formula?

 

[bigdan]

Thanks Sam.
This seems manual, right? Would one have to modify this formula for every row? In my spreadsheet there's over 100K lines :$

I am not sure if this work for your case.

Excel 2013/2016

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]7/5/2019[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]05-Jul-2019[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]B1[/TH]
[TD="align: left"]=SUMPRODUCT(--(A1=DATE(2019,7,5)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]A3[/TH]
[TD="align: left"]=TEXT(DATE(2019,7,5),"DD-MMM-YYYY")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sam_D_Ben]

Something like this.

Excel 2013/2016
	A	B	C	D	E
1	Date	Points
2	07/05/19	10		B-Date	07/05/19
3	07/06/19	22		E-Date	07/20/19
4	07/07/19	34		Net	66
Sheet28

Cell Formulas
Range	Formula
E4	=SUMPRODUCT(--(INT($A$2:$A$4)>=$E$2),--(INT($A$2:$A$4)<=$E$3),--($B$2:$B$4))