Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

IF criteria on two fields

Tim Meeks
Tim Meeks ✭✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

I have two IF statements that I can get to work by themselves but not together.

 

Here is the first one by itself. The formula is in a cell called “# of Days Past Due”. Both Part A and Part B work separately.

 

Part A - If the Date payment received is blank, it takes TODAYs date and subtracts from the Claim date, else 0.

=IF(ISBLANK([DATE PAYMENT RECEIVED]1), (TODAY() - [CLAIM DATE]1), 0)

 

Part B - The second argument I want to add to this formula looks at the STATUS column and if it is NOT REJECTED, it calculates, else 0.

=IF(STATUS2 <> "REJECTED", (TODAY() - [CLAIM DATE]2), 0)

 

Again both of the above work by themselves, but not when I combine them in one formula.

They recognize the cells, and don’t show an error, but they don’t reach the Else if they are FALSE and display 0.

 

=IF(OR(ISBLANK([DATE PAYMENT RECEIVED]3), STATUS3 <> "REJECTED"), TODAY() - [CLAIM DATE]3, 0)

 

One workaround I had was to go with the first formula, but if the STATUS is set to  REJECTED  then I made the # of DAYS PAST DUE font be the same as the cell color so it can’t be seen.

 

thanks for your help....

Tags:

Comments

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    Does this formula get you what you're looking for?  I used ISDATE.

     

    IF(STATUS7 <> "REJECTED", TODAY() - [CLAIM DATE]7, IF(ISDATE([DATE PAYMENT RECEIVED]7), (TODAY() - [CLAIM DATE]7), 0))

     

    https://app.smartsheet.com/b/publish?EQBCT=6b32f057a2d8414e88a7dd0938c84dee

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

    Brett,

     

    thanks for helping on this.  Actually, I need the second argument to occur if the DATE field is NOT populated, so that's why I was using ISBLANK.   But I took your logic of using the nested IFs and substitued ISDATE with ISBLANK and it still didn't work.  See below.

     

    =IF(STATUS1 <> "REJECTED", TODAY() - [CLAIM DATE]1, IF(ISBLANK([DATE PAYMENT RECEIVED]1), (TODAY() - [CLAIM DATE]1), ""))

     

    Like i said earlier, I have a work around by making the # of DAYS past due field and font the same color if the STATUS is REJECTED, but now i just want to try to figure it out for my pride and sanity.  Wink

     

    Good to hear from you and thanks again!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Tim,

     

    Try this:

     

    =IF(OR(STATUS23 = "REJECTED", ISDATE([DATE PAYMENT RECEIVED]23)), 0, TODAY() - [CLAIM DATE]23)

     

    for row 23.

    This will display 0 if either REJECTED or there is a payment received date.

    If not, display TODAY() - [CLAIM DATE]

     

    If you aren't doing anything with the negative (future) values, you can get rid of those too:

     

    =MAX(  formula from above    , 0)

     

    Hope this helps.

     

    Craig

     

     

     

     

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

    @Craig,

     

    that worked!  thanks for your help.  Here is the final formula I ended up using:

     

    =(IF(OR(STATUS1 = "REJECTED", STATUS1 = "VOIDED", ISDATE([DATE PAYMENT RECEIVED]1)), "", TODAY() - [CLAIM DATE]1 - 1))

     

    Had to add a condition for VOIDED and also subtracted one date from # of Days per user's request. Also, changed the from 0 to "" for a null value.

     

    For a item entered today, the formula calculates the # of Days as -1, however, i can live with that.

     

    thanks again!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Tim, 

     

    So is yesterday equal to -2?

    Shouldn't your Days Past Due = TODAY() - [CLAIM DATE]1 ?

     

    I might be missing a nuance.

     

    Craig

    ps: You are welcome.

     

     

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

    First of all, i'm always mising nuances. To answer your question, yesterday = 0.  In this case, I'm counting past due as a positive number.

    Let me explain.. no there is too much... let me sum up... Laughing

     

    The state by law has to pay within 45 days so this calculation is track how many (positive) days past due a claim is from the CLAIM DATE. However, the users don't want to start counting until they have 1 full day to process. 

     

    So since i'm subtracting 1 in the calculation, (TODAY() - [CLAIM DATE]1 - 1)))  per the example below, a Claim entered today would be -1, a claim entered yesterday would 0 days since they have not had 1 full day.  And a claim entered on 2 days ago is now 1 full days past due.

     

    For kicks and giggles, in the screen shot below, i entered a claim date for tomorrow, (3/15) which makes the Days Past Due = -2 but that shouldn't happen unless by mistake as TODAY should be the most current date entered.

     

    Comprende?

     

    Claim Date.JPG

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Brilliant Princess Bride reference!!

     

    I must admit that this does not make sense. 

    If [Claim Date] is the creation date, then they have 45 days to pay. Anything in the past should be positive. But if the governments says they need an extra day to take action, who am I to argue?

     

    Craig

     

     

This discussion has been closed.