Help

Re: If Then Statement populating

Solved
Jump to Solution
721 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Andrew_Strothei
4 - Data Explorer
4 - Data Explorer

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.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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:

Screen Shot 2020-02-01 at 6.38.28 AM

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")

See Solution in Thread

3 Replies 3
Andrew_Strothei
4 - Data Explorer
4 - Data Explorer

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.

Zero or a 1 is what I meant to say above.

Justin_Barrett
18 - Pluto
18 - Pluto

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:

Screen Shot 2020-02-01 at 6.38.28 AM

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")