help with dates

  • Thread starter Thread starter Dave_D
  • Start date Start date
D

Dave_D

I have a column of dates, formatted as dates mm/dd/yy. They are the
expiration dates for our club members. I would like to have a formula that
looks at the date, compares it to today, and inserts "expired" or "will
expire within 30 days" or "OK" in a separate column. I'm trying and not
even getting close.

Thanks for any help.
 
Dave_D said:
I have a column of dates, formatted as dates mm/dd/yy. They are the
expiration dates for our club members. I would like to have a formula that
looks at the date, compares it to today, and inserts "expired" or "will
expire within 30 days" or "OK" in a separate column. I'm trying and not
even getting close.

Thanks for any help.
Click to expand...


Just for starters, you know the command =today() will populate today's date
into a field?

You could have 'date' in the first column, '=today()' all the way down in
the second column and '=IF(B1>=A1,"OK","Expired")' in the third and copy it
down the column. Doesn't give you the three options you wanted, but it's a
start.
 
Dave_D said:
I have a column of dates, formatted as dates mm/dd/yy. They are the
expiration dates for our club members. I would like to have a
formula that looks at the date, compares it to today, and inserts
"expired" or "will expire within 30 days" or "OK" in a separate
column. I'm trying and not even getting close.

Thanks for any help.
Click to expand...


This is similar to Vladikavkaz's way but will cover all your outcomes. This
is slightly long winded and somebody may suggest a better way...

Say you have the command =today() in cell A1, and your expiry dates are in
column B.

I would have a hidden column C, and in C2 or whatever the first row is:

=$a$1-C2

Copy and paste this formula right down the column, and ensure it is
formatted as number, not a date.

As I say, hide column C so you can't see it.

Then in D, you put the nested IF command as follows (example for row 2):

=IF (C2<0, "Expired", IF (C2>30, "Expires in 30 days", "OK"))

I think this should work!
 
Or if you wanted to use a countdown on amount of days left till it expires
you could put
this in C2 =MROUND(A1-B2,1) and
this in D2 =IF(C2<0,"Expired",IF(C2>30,"OK","Expires in "&C2&" days"))
 
Dave_D said:
I have a column of dates, formatted as dates mm/dd/yy. They are the
expiration dates for our club members. I would like to have a formula that
looks at the date, compares it to today, and inserts "expired" or "will
expire within 30 days" or "OK" in a separate column. I'm trying and not
even getting close.
Click to expand...

=IF(A1>=TODAY(),"Expired",IF(A1+30>=TODAY(),"Will expire soon","OK"))


--
--
Fabian
Humans have to stop treating each other like they treat us ants. Think
about it. If we build, say, a pair of very tall structures, like two
anthills side-by-side, some stupid human swoops in out of nowhere and
knocks them down. Or humans will drop food on the ground near us. we
think it's for us, but those same humans will also try to kill us! I
have no sense of irony, I'm just an ant. But if I did, I'm sure I'd
notice that.
 
Back
Top