I would like to add a 'n/a' if cell K3 is left blank. =IF(2*(K3<$L$2)+1*(K3-$M$2<=$L$2)*(K3>=$L$2) =0,“OK”, IF(2*(K3<$L$2)+1*(K3-$M$2<=$L$2)*(K3>=$L$2)= 1,“DUE”,“OVERDUE”))

Comments

• As I look at that formula, though, I can hardly imagine that it IS valid. The term "2 * (K3 < $L$2)" for example, should only evaluate to "TRUE" or "FALSE", depending on whether or not K3 < L2.
   
  Obviously you're trying to calculate whether or not a thing is DUE or OVERDUE (or "On Time" or not returned, which is why I'm guessing that the K3 term is "Date Returned").
   
  If you'll post what all of the terms mean and how you're trying to figure this, I'll bet there's a MUCH simpler formula you can use. I doubt if this one is really working -- if it is -- the way you think.

  • Report
  

  by Cyanotic Wasp on August 22nd, 2009

• thanx.. K3 is the due date; $L$2 is todays date; $M$2 is the number 30 (ref..days)
  Formula works by checking the due date(K3);if past($L$2)then (L3) shows 'overdue'; if within 30 days,($M$2),then shows 'due';
  If due beyond 30 days then'OK, The issue was that there are sometimes comments in K3 or just left blank.
  Formula is in cell L3.
  Your soulution works,(thanks)if K3 is blank; but not if comments are entered. thanx in advance -dan-

  • Report
  

  by danielp1 on August 22nd, 2009

• If you would like to simplify your formula ...
   
  =IF( ISBLANK( K3), NA(), IF( K3 > NOW(), "Not Due", IF( K3 + $M$2 > NOW(), "Due", "Overdue by " & TEXT( NOW() - $M$2 - K3, "#,###") & " days")))
   
  Here are the conditions addressed, in order:
  1. No date has been entered into K3. (Use data validation to force numeric input in that cell.)
  2. The due date has not yet been reached.
  3. The thing is due because the due date has passed BUT the grace period has not been exceeded.
  4. The thing is overdue, and the days are counted.
   
  This formula also obviates the use of "L2" as "today's date", which is included in the formula as NOW(), eliminating a possible data entry error in L2 and aiding readability.
   
  PS: Excel allows you to use spaces at many points when entering formulas. You are strongly encouraged to do so, as it aids immensely in reading and troubleshooting. The formula I gave you can be entered exactly as it is written. I never could get yours to work

  • Report
  

  by Cyanotic Wasp on August 22nd, 2009

• =IF( ISBLANK( K3), NA(), IF( K3 > NOW(), "Not Due", IF( K3 + $M$2 > NOW(), "Due " & TEXT( K3 + $M$2 - NOW(), "#,##0") & " days left", "Overdue by " & TEXT( NOW() - $M$2 - K3, "#,##0") & " days")))
   
  Will also show how many days are left on the "due" grace period.

  • Report
  

  by Cyanotic Wasp on August 22nd, 2009

• Thanks for the explanation..makes more sense when you put it that way. I don't know why you could not get mine to work; It works for me..
  2*(K3<$L$2) equals 2 if the due date is less than (i.e., earlier than) today’s date. If this is true, the gage calibration is overdue.
  
  K3-$M$2<=$L$2 if the due date is within $M$2 days (in this case 30 days) of today’s date. K3 must also be greater than or equal to (later than or equal to) today’s date or else the gage is overdue. If both conditions are met, then 1*(K3-$M$2<=$L$2)*(K3>=$L$2) is equal to 1.
  
  
  Thanks again....now if I can just get cell to stay n/a when the operators put comments in the K3 cell! ;>)

  • Report
  

  by danielp1 on August 22nd, 2009