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](https://us.v-cdn.net/6031209/uploads/drupal_profile/files/pictures/ef/80/nef8073711bf9e3306bfe4a3026b63f29.jpg)
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....
Comments
-
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
-
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!
-
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
-
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!
-
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.
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives