Dates-Conversion

[Lisa12]

I am trying to have Excel look at two cells and verify if they are =. The
problem is one is formatted as a number and the second is formatted as a
date. How can I make it look at the number as a date?

A1 B2
2002 1/1/2002
2003 1/1/2003
2004 1/1/2004

I need to return a value from B3 if A1 is = to B2, but right now I am not
comparing a date to a date, and when I try to convert 2002 to a date, it
converts to 1905.

Please help.

Thank you,

Lisa12

 

[Peo Sjoblom]

Convert the date to a year instead

=IF(A1=YEAR(B2),B3,"")


--


Regards,


Peo Sjoblom

 

[Max]

One way
In C1, copied down: =TEXT(B1,"yyyy")=A1&""

 

[Lisa12]

What if I need A1 to look through B for the correct date? I would need a
vlookup for B2 and B3?


In other words if 2002 is in column B then return A
If 2003 is in 2003 then return B

Any ideas?

Thank you,

Lisa12

 

[ShaneDevenshire]

Sorry Lisa but your question is not clear. I think you need to be more
specific, what exactly do you mean by the "correct date"? And what do you
mean by "if 2003 is in 2003"?

In your original example you refer to A1, B2, and B3 (as column titles?) Do
you mean columns A, B, C?

 

[ward376]

This does what your original post asks - returns the value from b3 if
a1 is equal to b2.

=IF(A1-(VALUE(TEXT(B2,"yyyy")))=0,B3,"They're different.")

Or you can convert the value in column a to a date and go from there:

=VALUE("1/1/"&A2)

Cliff Edwards

 

[Lisa12]

Thank you all for your help, but I am not getting it. I think maybe I am
asking the wrong question, so I'll try again.

If I have column A with a number, and column B with a date, how can I use a
vlookup to find the number in B and return the information in C. I will
have to repeat this for each number in A.

So I will need to see if A1 is in B and if A2 is in B if not there is need
to return ""
Number Date Currency
A B C
2002
2005 1/1/2002 $4.00
2006 1/1/2003 $5.00
1/1/2004 $6.00
1/1/2005 $7.00
I hope this clears things up,

Please help

Lisa12

 

[vezerid]

I think you need this *array* formula, which finds the first date in
B:B, which belongs to a given year in A1:

=INDEX(B2:B10,MATCH(TRUE,YEAR(B2:B10)=A1,0))

As an array formula, commit with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

 

[vezerid]

Sorry, you want to return a currency amt. Hence you want to look into
C:C. Still an array formula:

=INDEX(C2:C10,MATCH(TRUE,YEAR(B2:B10)=A1,0))

HTH
Kostis

 

[Lisa12]

I am getting a value error referencing column B. Any ideas why?

Thank you,

Lisa12

 

[Lisa12]

I am able to retrieve the value if the number in A is in column B, but I
can't make the reply "" if it is not. Would I need to imbed the INDEX in an
IF stmt?

Thank you,

Lisa12

 

[vezerid]

Yes, enclose in an IF:

=IF(ISNUMBER(MATCH(TRUE,YEAR(B2:B10)=A1,0)),INDEX(C2:C10,MATCH(TRUE,YEAR(B2:B10)=A1,0)),"")

Regards,
Kostis