If functions and dates
 

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?

If functions and dates
 

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


--
Special-K
------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=562469

If functions and dates
 

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(B10,"Overdue!",0))))


"spunkyjon" wrote:

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?

If functions and dates
 

Another way,

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

EDATE requires the Analysis ToolPak to be installed. Go to
ToolsAddIns 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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=562469

If functions and dates
 

Thanks to all that have helped me out it worked like a charm. Thanks a million.

"tim m" wrote:

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(B10,"Overdue!",0))))


"spunkyjon" wrote:

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?