Formulas - IS Not Null & If Then

Question

&nbsp;
Hello!I have the following fields in my report:"Date Manager Entered""Last Day"I'd like to create a formula that does the following:1. See if there is a date entered in "Date Manager Entered"2. If there is a date in "Date Manager Entered" to calculate if "Date Manager Entered" - "Last Day" &lt;5 days3. If that calculation is &lt;5, display "No". If it's &gt;5, display "Yes"I was thinking of using: If(Is Not Null[Date Manager Entered], If(ToDays([Date Manager Entered]-[Last Day])&lt;5,"No","Yes"),"") but I'm pretty sure I'm not joining these correctly.Can you please help me determine what the proper formula should be? Also, please advise what sort of text type it should be.Thank you!

markshnier__you · Answer

try this as a formula text fieldIF(IsNull([Date Manager Entered]),"", //&nbsp; if empty, then result is empty quotes.&nbsp;// else we know there us a dateToDays([Date Manager Entered]-[Last Day]) &lt; 5, "No", "Yes")------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

vw · Answer

Thank you!&nbsp; This seems to be working.  &nbsp; What would be the formula to highlight the "no" answers to red?&nbsp; I already have 3 formulas for this report so I'm wondering if the conditional formatting can be built into the same formula as the one you've already  helped me with. &nbsp; Thank you! &nbsp;  &nbsp; Regards, &nbsp; Valerie  &nbsp;

vw · Answer

Sorry, if possible, I'd also like to update the formula to calculate the number of weekdays between the two dates.

I tried:

WeekdaySub([Last Day],[Date Manager Entered])

...but I get the error message that it's "expecting date but found number." However, both those fields are date as well as the formula.

So ultimately I'm looking to update below formula to calculate the number of weekdays between "Date Manager Entered" and "Last Day" as well as make the ones that come out with the answer "No" red font.

IF(
IsNull([Date Manager Entered]),"", // if empty, then result is empty quotes.
// else we know there us a date
ToDays([Date Manager Entered]-[Last Day]) < 5, "No", "Yes")

Thank you so much!

Regards,

Valerie

markshnier__you · Answer

Ok, try changing the field type to Formula Rich Text and try this.

IF(
IsNull([Date Manager Entered]),"", // if empty, then result is empty quotes.
// else we know there us a date
WeekDaySub([Last Day], [Date Manager Entered]) < 5, "<font color=red><b>No", "Yes")

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

vw · Answer

Hello Mark.&nbsp; Thanks for this.   &nbsp; For this formula, it's picking up the "&lt;font color=red&gt;&lt;b&gt;No" and placing that in the field instead of making it red. &nbsp;  &nbsp; Regards, &nbsp; Valerie  &nbsp;

Forum Discussion

Formulas - IS Not Null & If Then

6 Replies