Forum Discussion

VW's avatar
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!

Formulas and functions

6 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 years ago
    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's avatar
      VW
      Qrew Trainee
      2 years ago

      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's avatar
      VW
      Qrew Trainee
      2 years ago

      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's avatar
        MarkShnier__You
        Qrew #1 Challenger
        2 years ago
        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
        ------------------------------