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" <(E-Mail Removed)> wrote in message
news:7BD7EE3C-505C-4401-827E-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:7BD7EE3C-505C-4401-827E-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> > news:7BD7EE3C-505C-4401-827E-(E-Mail Removed)...
> > >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 there:

=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 there:
>
> =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 <Lis...@discussions.microsoft.com> 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 there:
>
> > =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 <veze...@act.edu> 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 <Lis...@discussions.microsoft.com> 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 there:
>
> > > =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 <veze...@act.edu> 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 <Lis...@discussions.microsoft.com> 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 there:
> >
> > > > =VALUE("1/1/"&A2)
> >
> > > > Cliff Edwards
>
>