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

Convert the date to a year instead

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


--


Regards,


Peo Sjoblom


"Lisa12" wrote in message
...
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

One way
In C1, copied down: =TEXT(B1,"yyyy")=A1&""
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lisa12" wrote:
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

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



"Peo Sjoblom" wrote:

Convert the date to a year instead

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


--


Regards,


Peo Sjoblom


"Lisa12" wrote in message
...
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 B3
2002 1/1/2002 A
2003 1/1/2003 B
2004 1/1/2004 C

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

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?

--
Cheers,
Shane Devenshire


"Lisa12" wrote:

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



"Peo Sjoblom" wrote:

Convert the date to a year instead

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


--


Regards,


Peo Sjoblom


"Lisa12" wrote in message
...
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 B3
2002 1/1/2002 A
2003 1/1/2003 B
2004 1/1/2004 C

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

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 the

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

Cliff Edwards

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

"ward376" wrote:

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 the

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

Cliff Edwards

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

On Jul 1, 8:30 pm, Lisa12 wrote:
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

"ward376" wrote:
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 the

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

Cliff Edwards

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

On Jul 1, 9:12 pm, vezerid wrote:
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

On Jul 1, 8:30 pm, Lisa12 wrote:

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

"ward376" wrote:
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 the

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

Cliff Edwards

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

Thank you,

Lisa12

"vezerid" wrote:

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

On Jul 1, 9:12 pm, vezerid wrote:
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

On Jul 1, 8:30 pm, Lisa12 wrote:

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

"ward376" wrote:
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 the

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

Cliff Edwards

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" wrote:

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

On Jul 1, 9:12 pm, vezerid wrote:
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

On Jul 1, 8:30 pm, Lisa12 wrote:

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

"ward376" wrote:
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 the

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

Cliff Edwards

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

On Jul 1, 10:15 pm, Lisa12 wrote:
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" wrote:
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

On Jul 1, 9:12 wrote:
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

On Jul 1, 8:30 pm, Lisa12 wrote:

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

"ward376" wrote:
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 the

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

Cliff Edwards