Date Value

L

lmullenjr

I am trying to use the Date Value function to convert a date to
numeric value and I am getting #VALUE! error message. Any ideas wh
this would be happening?

Drumme
 
B

Biff

Hi!

Explain how you are using it.

Datevalue( ) takes a TEXT argument. If you try to use a numeric argument
you'll get #VALUE!.

Biff
 
L

lmullenjr

I am using it in a SumIf equation to sum up data within a range of
dates. I thought I could use the Date Value function, but the actual
dates are numeric, not text. Any suggestions?
 
B

Biff

Suppose you have a list of dates in column A, A1:A10. In column B, B1:B10
are some sales figures:

4/1/2006...............300
4/2/2006...............744
4/3/2006...............818
4/4/2006...............522
4/5/2006...............900
4/6/2006...............312
4/7/2006...............444
4/8/2006...............777
4/9/2006...............602
4/10/2006.............788

You want to sum up the amounts between 2 dates, say, 4/1 to 4/5. Try any one
of these:

=SUMIF(A1:A10,">=4/1/2006",B1:B10)-SUMIF(A1:A10,">4/5/2006",B1:B10)

=SUMPRODUCT(--(A1:A10>=--"4/1/2006"),--(A1:A10<=--"4/5/2006"),B1:B10)

=SUMPRODUCT(--(A1:A10>=DATE(2006,4,1)),--(A1:A10<=DATE(2006,4,5)),B1:B10)

Better to use cells to hold the date criteria:

D1 = 4/1/2206
E1 = 4/5/2006

=SUMIF(A1:A10,">="&D1,B1:B10)-SUMIF(A1:A10,">"&E1,B1:B10)

=SUMPRODUCT(--(A1:A10>=D1),--(A1:A10<=E1),B1:B10)

Biff
 
Top