IF (AND(MONTH - with payment due date and payment frequency

Jörg Schmidt1

Hi,

I struggle with this formula. Do you have a plan ?

I want to calculate the amount depending on the payment frequency, f.e. yearly, half yearly, quarterly, monthly

Smartsheet Formel.jpg

Hope you can help 😃

Jason Albrecht

Hi @Jörg Schmidt1

You're absolutely right, and my apologies for the oversight.

I believe the following should now answer every condition:

Q1:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 1, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 4, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 2, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 3, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(Zahlweise@row = 4, Zahlbetrag@row, IF(AND(Zahlweise@row = 2, OR(ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 3)), Zahlbetrag@row, "")))))

Q2:

=IF(Falligkeit@row > DATE(YEAR(Falligkeit@row), 4, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 7, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 5, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 6, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, OR(ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 4)), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, "")))))

Q3:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 7, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 10, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 8, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 9, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, "")))))

Q4:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 10, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row) + 1, 1, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 11, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 12, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, 0)))))

For ease of reference, the formulas can be broken down into the following parts:

1. =IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 1, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 4, 1), Look at the due date and determine if it falls within the respective quarter,
2. IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 2, 1)), If true, then look at the payment frequency to make sure it doesn't equal 12, or look at the due date to see if it falls within the first month of the respective quarter.
3. Zahlbetrag@row, If either of these are true, display the payment amount
4. IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 3, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), Otherwise (in other words, if payment frequency is 12 and falls outside the first month but is still inside the quarter), look at the due date to see if it is the second month of the quarter and multiply the payment amount by 2, or if the due date is in the third month of the quarter, multiply the payment amount by 3
5. IF(Zahlweise@row = 12, Zahlbetrag@row * 3, If the due date is not within the respective quarter, but the payment frequency is 12, then display the payment amount multiplied by 3 (for the 3 months in the quarter).
6. IF(Zahlweise@row = 4, Zahlbetrag@row, If the payment frequency is quarterly (4), display the payment amount.
7. IF(AND(Zahlweise@row = 2, OR(ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 3)), Zahlbetrag@row, ""))))) Finally, if the payment frequency is biannually (2), and the due date is six months before the due date, display the payment amount, otherwise, don't display anything.
8. 
   

Jason Albrecht

Hello @Jörg Schmidt1

Thank you for your question.

May I please confirm what I think you are trying to achieve?

I am reading the formula like this:

If the month of the Falligkeit (Maturity Date) is less than 3 and the Zahlweise (Paid) number equals 12, then multiply Zahlbetrag (Amount Payable) by 3, but if those conditions are not met, check to see if Paid equals 1, and if it does equal 1, put the Amount Payable.

If I am reading it right, then I'm not seeing what you want the formula to do when Falligkeit (Maturity Date) equals 12. In the first line, neither the Falligkeit (12) or Zahlweise (12) meets the condition of your formula.

To help further, I would need to confirm if Zahlweise is the number of payments in a year. For instance, does 12 mean there are 12 monthly payments. If so, does that mean that the number 1 represents just on annual payment?

It may help to also upload the picture with the amounts you are expecting to see in Q1, Q2, Q3, to help us replicate and troubleshoot your formula.

Thanks for providing this puzzle.

Jörg Schmidt1

Dear @Jason Albrecht @Andrée Starå ,

depending on my screenshot above and your questions

if Zahlweise (payment frequency) would be 12 = monthly the result should be 16,90 € * 3 in Q1/Q2/Q3 and Q4

if Zahlweise would be different from 12 f.e. = 1 (yearly) payment should be equal to "Zahlbetrag"177,31 €

and depending on "Fälligkeit" (due date) appear in either Q1 - Q4. Or in case of "Zahlweise"2 (= half yearly) it should appear twice in Q1-Q4 and so on.

Hope I could make it more clear now ??😀

Jason Albrecht

Hi @Jörg Schmidt1

Thank you for the clarification. I think I understand now.

Basically you're looking to have each relevant Quater Column show the total amount owing for that quarter, which is dependent on the due date and payment frequency.

I'm assuming Q1-4 is based on a calendar year.

The following formula could be used in Q1 column (where Q1 is expected to be from 01-JAN-22 to 31-MAR-22 and you only want to show the relevant monthly amount (i.e., if the contract is finalising in the second month of the first quarter, you only want two months, not three months' multiple of the payment)):

=IF(Falligkeit@row >= DATE(2022, 1, 1), IF(Falligkeit@row < DATE(2022, 4, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(2022, 2, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(2022, 3, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, 0)))))

The following formulas for Q2-4 uses the Prorate formula, but needs refining, to filter out the yearly and half-yearly results when not applicable. I'll revert back to these when I have some time, otherwise, this is hopefully a good starting point for you or @Andrée Starå or others in the community to finish:

Q2:

=PRORATE(Zahlbetrag@row * Zahlweise@row, Falligkeit@row - 366.25, Falligkeit@row, IF(Zahlweise@row = 1, DATE(YEAR(Falligkeit@row) - 1, MONTH(Falligkeit@row) - 1, DAY(Falligkeit@row)), IF(Zahlweise@row = 2, DATE(2022, 1, 1), DATE(2022, 3, 31))), DATE(2022, 6, 30) + IF(Zahlweise@row = 2, 3, 0), 2)

Q3:

=PRORATE(Zahlbetrag@row * Zahlweise@row, Falligkeit@row - 366.25, Falligkeit@row, IF(Zahlweise@row = 1, DATE(YEAR(Falligkeit@row) - 1, MONTH(Falligkeit@row) - 1, DAY(Falligkeit@row)), IF(Zahlweise@row = 2, DATE(2022, 3, 31), DATE(2022, 7, 1))), DATE(2022, 9, 30), 2)

Q4:

=PRORATE(Zahlbetrag4 * Zahlweise4, Falligkeit4 - 366.25, Falligkeit4, DATE(2022, 10, 1), DATE(2022, 12, 31), 2)

Jörg Schmidt1

Dear Jason, @Jason Albrecht

thanks for your extensive reply 😀 I really appreciate your help

I had to change your formula a little bit due to German rules

• Fälligkeit instead Falligkeit ("ä") meaning when is invoice coming (with 12 it does not matter because it should be the same amount from Q1-Q4)
• ";" instead of ","

and added them in Q1-Q4 but partly get "invalid value".

Any idea why:

in row 1 = correct

row 2 = should be 177,31 € in Q2 (due to Fälligkeit 10.04.2023 an Zahlweise = 1)

row 3 ) should be 59,99 € in Q4 (due to Fälligkeit 04.12.2023 and "Zahlweise" = 1

Jörg Schmidt1

Dear @Jason Albrecht , dear @Andrée Starå , would you be so kind to check my latest entry.

Thank you so much :-)

Jason Albrecht

Hi @Jörg Schmidt1

Apologies. End-of-month is a busy time for us. I'll try doing a review over the weekend.

Meanwhile, maybe you can reply with screenshots of the formulas in the offending cells?

I just want to confirm the dates for each quarter have been adjusted correctly.

Also, is Fälligkeit set for dd.mm.yy or mm.dd.yy, please?

Jörg Schmidt1

Q4.png

Q3.png

Q2.png

Dear @Jason Albrecht thanks for your reply and no apologies, please. I am so grateful for your help.

Yes, "Fälligkeit" is set for dd.mm.yy (European).

I guess my fault is in the understanding of "366,25" ??

Jason Albrecht

Hi @Jörg Schmidt1

Thank you for your patience as I've been working through this (almost from scratch, as I had to remember the premise for some of the functions).

I continued to work on the formula in the first quarter and adjust it to work in the other three quarters.

So, for now, we don't need to rely on the Prorate function. That said, in answer to your question about "366,25" in the formula, it tells the function that the start date is one year earlier (i.e., actual days in a year is 365.25, plus one more day in order to count the date due).

I will continue to work on the Prorate formulas for each quarter, but for now the following should work (apologies in advance for you needing to edit spelling to "Fälligkeit" and substitute "," for ";"):

Q1:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 1, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 4, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 2, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 3, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, 0)))))

Note: The Date function is now extracting the Year from Fälligkeit (due date), so that it remains dynamic for the respective calendar year. In other words, whether Fälligkeit is in 2022, 2023, 2024 or any other year, this formula should now work across all years. This also applies to the following Quarter formulas. This means that you will need to be careful and not simply sum all Q1 results and assume they will all land in the same year.

Q2:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 3, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 7, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 5, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 6, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, 0)))))

Q3:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 7, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 10, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 8, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 9, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, 0)))))

Q4:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 10, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row) + 1, 1, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 11, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 12, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, 0)))))

Jörg Schmidt1

Dear @Jason Albrecht ,

I am so sorry to bother you again with this issue, but I am quite sure you are close enough to the solution.

If you look at "Zahlweise" = 1 (yearly) it seems easy because there is only one amount in the quarter depending on the due date (=Fälligkeit)

if you look at "Zahlweise" = 12 (monthly) it also seems easy because you do not need a due date at all. You onlx have to calculate the "Zahlbetrag" * 3 and have to put this in every quarter. There is no quarter with a smaller amount.

But than: If you look at the other Zahlweise f.e. = 2 (half yearly oder quarterly) this means the "Zahlbetrag has to appear in the right quarter depending on the "Fälligkeit" and you put the same amount in the quarter + Zahlweise (f.e. Zahlweise = 2 starting 1.2.2023 means 100,- EUR in Q1 and 100,- € in Q3 - right ?

Hope I could show the point - still motivated to find the final solution. I would feat you :-))

Jason Albrecht

Hi @Jörg Schmidt1

I’ll have a look at this section of the formula, hopefully later today:

ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1)

I believe for the Q1 formula it should = 1 or 3 and for Q2 it should = 2 or 4, which means embedding this section in an OR function.

Jason Albrecht

Hi @Jörg Schmidt1

You're absolutely right, and my apologies for the oversight.

I believe the following should now answer every condition:

Q1:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 1, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 4, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 2, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 3, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(Zahlweise@row = 4, Zahlbetrag@row, IF(AND(Zahlweise@row = 2, OR(ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 3)), Zahlbetrag@row, "")))))

Q2:

=IF(Falligkeit@row > DATE(YEAR(Falligkeit@row), 4, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 7, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 5, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 6, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, OR(ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 4)), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, "")))))

Q3:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 7, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 10, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 8, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 9, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, "")))))

Q4:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 10, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row) + 1, 1, 1), IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 11, 1)), Zahlbetrag@row, IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 12, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, 0)))))

For ease of reference, the formulas can be broken down into the following parts:

1. =IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 1, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 4, 1), Look at the due date and determine if it falls within the respective quarter,
2. IF(OR(Zahlweise@row <> 12, Falligkeit@row < DATE(YEAR(Falligkeit@row), 2, 1)), If true, then look at the payment frequency to make sure it doesn't equal 12, or look at the due date to see if it falls within the first month of the respective quarter.
3. Zahlbetrag@row, If either of these are true, display the payment amount
4. IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 3, 1), Zahlbetrag@row * 2, Zahlbetrag@row * 3)), Otherwise (in other words, if payment frequency is 12 and falls outside the first month but is still inside the quarter), look at the due date to see if it is the second month of the quarter and multiply the payment amount by 2, or if the due date is in the third month of the quarter, multiply the payment amount by 3
5. IF(Zahlweise@row = 12, Zahlbetrag@row * 3, If the due date is not within the respective quarter, but the payment frequency is 12, then display the payment amount multiplied by 3 (for the 3 months in the quarter).
6. IF(Zahlweise@row = 4, Zahlbetrag@row, If the payment frequency is quarterly (4), display the payment amount.
7. IF(AND(Zahlweise@row = 2, OR(ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 3)), Zahlbetrag@row, ""))))) Finally, if the payment frequency is biannually (2), and the due date is six months before the due date, display the payment amount, otherwise, don't display anything.
8. 
   

Jörg Schmidt1

Dear Jason, @Jason Albrecht

thank you so much for your support. I really appreciate your effort.

One last hint: When I want to show the amounts for monthly payment in every quarter I have to choose

December-31-year - otherwise the formula does not work.

But it´s done. Again I want to thank you :-) Great job😀

Jason Albrecht

Hi @Jörg Schmidt1

If I understand your comment, you are saying that when Zahlweise is 4, the amount in Zahlbetrag is a monthly amount and not a quarterly amount?

If so, then you can change IF(Zahlweise@row = 4, Zahlbetrag@row, to be IF(Zahlweise@row = 4, Zahlbetrag@row*3,

Or, have I misunderstood your last comment?

You'll notice in my last screenshot, in row 21 where Zahlweise is 4 and Falligkeit is 10-Oct-2024 that all Q's were filled, since October falls in the last quarter of the calendar year.

You'll also notice that in rows 22, 23 and 24 they all have Zahlweise as 12 but Falligkeit finalises in October, November or December, which means the amount due in Q4 would either be 1, 2 or 3 months worth.

I've appreciated this challenge and would like to make sure it's working just right 😊

Jörg Schmidt1

Dear @Jason Albrecht ,

monthly payment is "Zahlweise" = 12, quarterly is = 4

therefore the result in your screenshot in rows 4-6, 10-12, 16-18 and 22-23 is still wrong whereas

row 24 is right because the result is 50,70 € in every quarter (when Zahlweise is monthly

as said above the due date is not relevant with "Zahlweise = 12",

with "Zahlweise" = 4 (quarterly) and "Zahlbetrag" = 50,70 € the same result would appear in every quarter

Wish you a healthy and lucky weekend :-)

Jason Albrecht

Hello @Jörg Schmidt1

Thank you for the additional clarification. I must admit I missed your comment about the due date not relevant when Zahlweise = 12. As a result, please see the simplified formulas below. I am curious though, as to why it's not relevant?

Q1:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 1, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 4, 1), IF(Zahlweise@row <> 12, Zahlbetrag@row, Zahlbetrag@row * 3), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(Zahlweise@row = 4, Zahlbetrag@row, IF(AND(Zahlweise@row = 2, OR(ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 3)), Zahlbetrag@row, "")))))

Q2:

=IF(Falligkeit@row > DATE(YEAR(Falligkeit@row), 4, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 7, 1), IF(Zahlweise@row <> 12, Zahlbetrag@row, Zahlbetrag@row * 3), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, OR(ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 4)), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, "")))))

Q3:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 7, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row), 10, 1), IF(Zahlweise@row <> 12, Zahlbetrag@row, Zahlbetrag@row * 3), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, "")))))

Q4:

=IF(Falligkeit@row >= DATE(YEAR(Falligkeit@row), 10, 1), IF(Falligkeit@row < DATE(YEAR(Falligkeit@row) + 1, 1, 1), IF(Zahlweise@row <> 12, Zahlbetrag@row, Zahlbetrag@row * 3), IF(Zahlweise@row = 12, Zahlbetrag@row * 3, IF(AND(Zahlweise@row = 2, ROUNDUP(MONTH(Falligkeit@row) / 3, 0) = 1), Zahlbetrag@row, IF(Zahlweise@row = 4, Zahlbetrag@row, 0)))))