DateValue function

Folks,
Can the DATEVALUE function reference a cell address such as A3 rather than be hard coded?
Frank FreeseAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
Your problem is with Range("C2"). This is VBA syntax, and does not work in a worksheet formula. The worksheet equivalent is INDIRECT("C2").

What I suggest instead is a regular formula using SUMPRODUCT:
=SUMPRODUCT((C2=$A$3:$A$40)*(D2=$B$3:$B$40))     if C2 is a date formatted like you showed
=SUMPRODUCT((DATEVALUE(C2)=$A$3:$A$40)*(D2=$B$3:$B$40))        if C2 is text
0
 
byundtCommented:
Of course it can.

What data does the cell contain in your workbook that DATEVALUE cannot convert?
0
 
Frank FreeseAuthor Commented:
Let's say A3:A40 are dates: 11/01/2013 - 11/30/2013
In cell C2 I would like to enter in a date: 11/15/2013
In cells B3:B40 are various product numbers many repeated for some products might have more sales during this time period.
In cell D2 I enter in a product number: K7896
F2 will tell me how many of a product I sold in that day.
I tried to do this in F2:
{=SUM((DATEVALUE(Range("C2"))=$A$3:$A$40)*(Range("D2")=$B$3:$B$40)}

I get a #VALUE error in F2

I know I could use filters but I'm trying to do this with a formula.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Frank FreeseAuthor Commented:
One question here....would this be an array formula?
0
 
byundtCommented:
The SUMPRODUCT formula is an array formula that does not need to be array-entered.

If the formula is not working for you, could you please post a sample workbook that demonstrates the problem? It doesn't need more than five rows of data.
0
 
Frank FreeseAuthor Commented:
I answered my own last question.
Great...two options for one problem.
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.