IF criteria on two fields

Tim Meeks

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

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

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.  

 

Good to hear from you and thanks again!

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

@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

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

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

 

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?

 

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