VW
Qrew Trainee
2 years ago

# Formulas - IS Not Null & If Then

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" <5 days
3. If that calculation is <5, display "No". If it's >5, display "Yes"

I was thinking of using: If(Is Not Null[Date Manager Entered], If(ToDays([Date Manager Entered]-[Last Day])<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!

### 6 Replies

• try this as a formula text field

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")

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
• VW
Qrew Trainee

Thank you!  This seems to be working.

What would be the formula to highlight the "no" answers to red?  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.

Thank you!

Regards,

Valerie

• VW
Qrew Trainee

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

• 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
------------------------------