Hi Helping community,
I have an issue with a formula, I don't have the knowledge to make it work cleanly.
This works but it's to simple for what I am trying to acheve, I would like cell B3(Days Overdue) to be blank if the due date has not been met and the same for if Cell B1(Due date) is left blank?
If anyone could help, it would be amazing.
Many thanks,
Jos
EDIT: I needed to add a little more to the formulas for if the due date was blank and to avoid blue warning triangles.
If the duration in the "days overdue" cell is for display purposes only and will not be used as a duration value in a downstream formula,
=IF(OR(B1="",B2=""),"",IF(B2>B1,B2−B1,""))
If the value will be used in downstream formulas that expect it to be a duration, a null string from the above formula will create an error. One option is
=IF(OR(B1="",B2=""),DURATION(0),IF(B2>B1,B2−B1,DURATION(0)))
or
=IF(OR(B1="",B2=""),DURATION(0),MAX(B2−B1,DURATION(0)))
then set up a "custom highlight" of "when duration equals 0ms, set text opacity to 0" which will make it invisible. The text opacity can be set by clicking on the color wheel to the right of B I U S to bring up the color wheel then dragging the opacity slider to zero.
EDIT: I needed to add a little more to the formulas for if the due date was blank and to avoid blue warning triangles.
If the duration in the "days overdue" cell is for display purposes only and will not be used as a duration value in a downstream formula,
=IF(OR(B1="",B2=""),"",IF(B2>B1,B2−B1,""))
If the value will be used in downstream formulas that expect it to be a duration, a null string from the above formula will create an error. One option is
=IF(OR(B1="",B2=""),DURATION(0),IF(B2>B1,B2−B1,DURATION(0)))
or
=IF(OR(B1="",B2=""),DURATION(0),MAX(B2−B1,DURATION(0)))
then set up a "custom highlight" of "when duration equals 0ms, set text opacity to 0" which will make it invisible. The text opacity can be set by clicking on the color wheel to the right of B I U S to bring up the color wheel then dragging the opacity slider to zero.
Thanks so much, you're a genius and have made my life so so much easier.
Many thanks.
Jos
Formula for number of days overdue.
