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
1	7/5/2019	1
2
3	05-Jul-2019

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =SUMPRODUCT(--(A1=DATE(2019,7,5))) A3 =TEXT(DATE(2019,7,5),"DD-MMM-YYYY") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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))