Discussions

 View Only
  • 1.  Formulas - IS Not Null & If Then

    Posted 09-14-2022 23:08
    Edited by V W 09-22-2022 19:39

     

    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!



  • 2.  RE: Formulas - IS Not Null & If Then

    Posted 09-15-2022 03:03
    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
    ------------------------------



  • 3.  RE: Formulas - IS Not Null & If Then

    Posted 09-15-2022 14:01

    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

     






  • 4.  RE: Formulas - IS Not Null & If Then

    Posted 09-15-2022 14:19

    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

     






  • 5.  RE: Formulas - IS Not Null & If Then

    Posted 09-15-2022 16:13
    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
    ------------------------------



  • 6.  RE: Formulas - IS Not Null & If Then

    Posted 09-15-2022 16:20

    Hello Mark.  Thanks for this.

     

    For this formula, it's picking up the "<font color=red><b>No" and placing that in the field instead of making it red.

     

     

    Regards,

     

    Valerie

     






  • 7.  RE: Formulas - IS Not Null & If Then

    Posted 09-15-2022 16:44

    Hi Mark,

     

    You can ignore my last message.  I changed the format to rich text.  Thank you!

     

     

    Regards,

     

    Valerie