Datevalue - Same value, one returns error?

**Jaymond Flurrie** · 05-07-2013, 10:26 AM

I have a value in a cell named period. It shows in formulabar 1.4.2012 (April 1st 2012). I try formula
=DATEVALUE("1.4.2012")
gives 41000 exactly like it should.
=Period=DATEVALUE("1.4.2012")
returns TRUE exactly like it should.

However,
=DATEVALUE(Period)
returns #VALUE!

I don't get it. Naturally I want that lower one to work.

edit. Also
=DATE(YEAR(Period);MONTH(Period);1) works fine, but
=DATEVALUE(DATE(YEAR(Period);MONTH(Period);1))
does not

**Jonmo1** · 05-07-2013, 10:29 AM

Datevalue converts a TEXT string to a Date.
Your Named Range period contains a DATE not a text string.
So Datevalue(Period) errors, because Datevalue is looking for a TEXT string.

so instead of
=DATEVALUE(Period)
you would just write
=Period

**Jaymond Flurrie** · 05-07-2013, 10:38 AM

Originally Posted by Jonmo1

Datevalue converts a TEXT string to a Date.
Your Named Range period contains a DATE not a text string.
So Datevalue(Period) errors, because Datevalue is looking for a TEXT string.

so instead of
=DATEVALUE(Period)
you would just write
=Period

Thanks for the answer!

I need to match the given date to an array of dates named TestName, currently the array has one date, April 1st 2012. Datevalue of it is 41000 i.e. a match. Now when I input a formula like:
=MATCH(DATE(YEAR(Period);MONTH(Period);1);TestName;0)
or
=MATCH(Period;TestName;0) (Which would be the ideal case)
or
=MATCH(DATEVALUE(Period);TestName;0)

I get #VALUE!

Maybe Datevalue is not the function I should use?

**Jonmo1** · 05-07-2013, 10:43 AM

Originally Posted by Jaymond Flurrie

Thanks for the answer!

I need to match the given date to an array of dates named TestName, currently the array has one date, April 1st 2012. Datevalue of it is 41000 i.e. a match.

If Datevalue returns 41000 for the date in the TestName range, then that means that it IS a text string.
But again, Period is NOT a Text string.
= No Match.

I think you should attach a sample book.

**Jaymond Flurrie** · 05-07-2013, 10:57 AM

I hope this one clears it up. Note that the TestName on row 2 has to be expandable (otherwise the match function is - obviously - needless).

edit. Also, it is worth of noting that the A1 has to be displayed in that format (MM/YYYY) and that the TestName values are coming from SQL Server - although I'm free to modify the query anyway I want.

**JosephP** · 05-07-2013, 11:24 AM

your testname data is text so you either have to convert the lookup value to text (using the text function, and matching formats) or convert the testname data to dates which will only work if you have more than one cell (or the MATCH function won't work). for instance you can redefine the testname data to use
=DATEVALUE(Sheet1!$A$2:$A$3)
then use your match function