Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 31, 2020 03:51 PM
I am trying to write what I believe would be best as an IF THEN statement but I’m open to suggestions.
I have a column named client payment date and there’s a number, 1-31 there.
I want it to print out in a different column (Which I’ve named “Payment Transfer Date”) one of three things.
If the number under “Client Payment Date” column is between 1-10 then I’d like the “Payment Transfer Date” column to display 15th.
If the number under “Client Payment Date” column is between 11-20 then I’d like the “Payment Transfer Date” column to display 25th.
If the number under “Client Payment Date” column is between 21-31 then I’d like the “Payment Transfer Date” column to display 5th.
Any help would be greatly appreciated as I feel I’ve gone above the skill level on what I know.
Solved! Go to Solution.
Feb 01, 2020 06:52 AM
Welcome to the community, @Andrew_Strotheide! :grinning_face_with_big_eyes: When trying to find where something falls in a sequence, you can also work the comparisons in sequence—either highest to lowest, or lowest to highest—and you don’t need to use AND()
anywhere in the mix. (Your use of AND is also malformed, but I’ll get to that later).
To start with the lowest range first, it would be structured like this:
IF({Client Payment Date} < 11, "15th", IF({Client Payment Date} < 21, "25th", "5th"))
To break this down, we first check to see if it’s less than 11, meaning anywhere between 1 and 10. If that is true, we know we want to output “15th”, and the rest of the comparisons are skipped. If not, moving to the next comparison and checking for a value less than 21 effectively means it must be between 11 and 20 because the first comparison failed. If that’s true, we output “25th”. If not, only possible option is that it’s 21 or higher, so we automatically output “5th”.
To work from the other end, it would be structured like this:
IF({Client Payment Date} > 20, "5th", IF({Client Payment Date} > 10, "25th", "15th"))
Either way, this is your output:
Now, for situations where you need to use AND()
, you put all comparisons inside the AND()
function, like this (random example not related to your use case):
IF(AND(Color="Blue", Value=10), "Blue 10", "Something else")
Jan 31, 2020 05:56 PM
IF({Client Payment Date}>=11, AND({Client Payment Date}<=20,“25th”))
This is what I have so far, but it’s displaying a 0 or a zero in the “Payment Transfer Date” column.
Jan 31, 2020 06:12 PM
Zero or a 1 is what I meant to say above.
Feb 01, 2020 06:52 AM
Welcome to the community, @Andrew_Strotheide! :grinning_face_with_big_eyes: When trying to find where something falls in a sequence, you can also work the comparisons in sequence—either highest to lowest, or lowest to highest—and you don’t need to use AND()
anywhere in the mix. (Your use of AND is also malformed, but I’ll get to that later).
To start with the lowest range first, it would be structured like this:
IF({Client Payment Date} < 11, "15th", IF({Client Payment Date} < 21, "25th", "5th"))
To break this down, we first check to see if it’s less than 11, meaning anywhere between 1 and 10. If that is true, we know we want to output “15th”, and the rest of the comparisons are skipped. If not, moving to the next comparison and checking for a value less than 21 effectively means it must be between 11 and 20 because the first comparison failed. If that’s true, we output “25th”. If not, only possible option is that it’s 21 or higher, so we automatically output “5th”.
To work from the other end, it would be structured like this:
IF({Client Payment Date} > 20, "5th", IF({Client Payment Date} > 10, "25th", "15th"))
Either way, this is your output:
Now, for situations where you need to use AND()
, you put all comparisons inside the AND()
function, like this (random example not related to your use case):
IF(AND(Color="Blue", Value=10), "Blue 10", "Something else")