Using a Date in Excel Formula without using VBA -
i trying combine 4 formulas work independently 1 formula provide information users in regards status of task in excel (this have available work with).
cell b6: project due date: 9/12/15
cell b7: document received date: filled in user
the document must received -150 days of project due date
formulas , result:
if(and(b6="",b7=""),"","")
this works
if(and(b6="",b7<>"",today()=b7-150),"due today","")
this works
if(and(b6="",b7<>"",today()>b7-150),"overdue","")
this works
if(and(b6="",b7<>"",today()>=b7-155,today()<=b7-151),"approaching due date","")
this works
=if(b6<>"","complete","")
this works
unforunately can't have 5 cells determine status, naturally combine them 1 cell , can't come right formula.
this have put #value! error
=if(and(b6="",b7=""),"",""),if(and(b6="",b7<>"",today()=b7-150),"due today",""),if(and(b6="",b7<>"",today()>b7-150),"overdue",""),if(and(b6="",b7<>"",today()>=b7-155,today()<=b7-151),"approaching due date",""),if(b6<>"","complete","")
can me catch i'm missing?
thank in advance
try one:
pretty using nested if statements, not pretty gets job done. assuming stated due date in b6 , document received in b7.
=if(b7="",if(today()=b6-150,"due today",if(today()>b6-150,"overdue",if(today()>=b6-155,"approaching due date","still have time"))),"error")
edit: added clarity , code tags.
Comments
Post a Comment