How do I use an "IF" formula using dates in excell?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I use an "IF" function involving dates. For Example: (IF (a date in
one cell ends in 2005 THEN "x"),IF( a date in one cell ends in 2006 THEN
"Y"), " ")
 
Assuming these cells are really dates and by 'ends in' you mean 'is in the
year', then you would do something like
=if(year(a1)=2005,true_value,false_value). If they're really strings that
end in 2005, then =if(right(a1,4)="2005",true_value,false_value)
 
Thank You so much I have been trying to figure out that formula on and off
for weeks. Do You work for Microsoft or do you just provide free advice
to people?
 
If you have the time I have a a really complicated and Puzzling Excell
question that also involves the "IF" formula between two worksheets. Do you
have an e-mail you would be willing to communicate over or do you perfer the
Microsoft "Chat room"?
 
Maybe you can help. Saw this and this is my question.

I have a date say 1/1/2005 in say cell J7; I want to write an if formula
that says if J7 is less than 7/1/2006 then it returns "expired" and if not it
returns "current"

Thanks
 
That works! Thanks so much but could you tell me why you couldn't simply say
=if (J7 < 7/1/2006, "expired", "current")
 
7/1/2006 = 7 divided by 1 divided by 2006 (=0.00349). It's not a date.

By using date(2006,7,1) or having excel coerce the text "7/1/2006" (with +0) to
a date, the formula could evaluate the way you want.
 
Hi-
I'm trying to do the same thing, however, I need to use "TODAY'" in the formula.

I have subcontractors who provide me with a certificate of insurance. I need a formula to tell me whether their certs are current or expired.

I've tried this =IF(D8>"TODAY","Current","Expired") but it's not working properly. It leaves the word Expired in the cell but won't change to Current if cell D8 is greater than Today.

Any suggestions?


7/1/2006 = 7 divided by 1 divided by 2006 (=0.00349). It's not a date.

By using date(2006,7,1) or having excel coerce the text "7/1/2006" (with +0) to
a date, the formula could evaluate the way you want.

Woodloch wrote:
>
> That works! Thanks so much but could you tell me why you couldn't simply say
> =if (J7 < 7/1/2006, "expired", "current")
>
> "daddylonglegs" wrote:
>
> >
> > Try this
> >
> > =IF(J7="","",IF(J7<"7/1/2006"+0,"expired","current"))
> >
> >
> > --
> > daddylonglegs
> > ------------------------------------------------------------------------
> > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> > View this thread: http://www.excelforum.com/showthread...hreadid=498736
> >
> >
 
Back
Top