If functions and dates

S

spunkyjon

I have a list of dates in column A. These dates are when the next
safety check will (or should) take place. In some cases the safety
checks have been missed and are overdue.

What i would like is some kind of function in column B to state whether
the safety check date is overdue, less than 3 months away, less than 6
months away or more than 6 months away.

I have tried in vain to do this with an IF function. Can someone help?
 
S

Special-K

Try this

=IF(A1<NOW(),"OVERDUE",IF(A1<NOW()+90,"LESS 3
MTHS",IF(A1<NOW()+180,"LESS 6 MTHS","MORE 6 MTHS")))

Comparing a date entered in column A with the date/time now and results
in column B

Regards
Special-K
 
T

tim m

special K beat me to it, but since I played around with this I'm going to
post my formula anyway. :O)-->

I put the following formula in column B: =TODAY()-A1 (You could hide this
column or put this formula in any other blank column.

I then put this formula in column C

=IF(B1<-180,"Over 6 months",IF(B1<-90,"Between 3 and 6
months",IF(B1<0,"Between now and 3 months",IF(B1>0,"Overdue!",0))))
 
S

SteveG

Another way,

=IF(TODAY()>A1,"Overdue",IF(A1<EDATE(TODAY(),3),"Less than
3",IF(A1<EDATE(TODAY(),6),"Less than 6","More than 6")))

EDATE requires the Analysis ToolPak to be installed. Go to
Tools>AddIns and select the Analysis ToolPak click Ok.

The difference here is that TODAY is not looking for a time and EDATE
will take into consideration months with 31 days and also less than 30
(feb). 90 or 180 may not truly be a full 3 months or 6 months. e.g.
2/1/2006 - 5/1/2006 is only 89 days but represents 3 months. Not sure
if that matters at all.


HTH

Steve
 
Top