• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple If or AND functions on a single cell.

B

Big_Daz

New Member
Ladies and gents,


I need your advice on the cleanest formula/method of setting cell content base on a date.


I have a date that a service is due.

The service may be overdue (negative number, due in the next 30 days,90 days or in the future.


I have column A which has the due date, column B has the number of days due/overdue using the formula Bx=(-(TODAY()-Ax).

Column C needs to have Overdue, Next Month, 3 Months or Future.


This is easily done using single AND statements eg, overdue can be =IF(Ax<TODAY(),"OVERDUE","FUTURE")but I am having a complete mind blank on how to do this for 4 possible outcomes.


I need the days due/overdue and if overdue, next month, 3 months, future to be simple as it is pulled into a pivot in another workbook.


All help gratefully received as its the end of the day and my mind is in reverse !!


Many Thanks


Big_Daz
 
You can keep stringing the IF statements together:

=IF(B2<0,"OVERDUE",IF(B2<=30,"NEXT MONTH",IF(B2<=90,"3 MONTHS","FUTURE")))


Alternate method, if you want, would be to seutp a lookup table somewhere like this:

-99999____OVERDUE

0_________NEXT MONTH

31________3 MONTHS

61________FUTURE


And then run a LOOKUP from col B against the table. This is convenient when you have a long list of possible outputs, you want to see what the criteria is, or you want to change the criteria easily.
 
Luke M,


Thanks for the swift answer, I knew you could string them together, just the grey matter refusing to play.


I will give it a go when i get home


Daz
 
You must log in or register to reply here.
Back
Top