If/Then formula

Kelly Cepicky

I need to perform a calculation from 3 columns if criteria in a third column is met. What is the formula? Any help is appreciated!

Nick Korna

Totally possible, though the formula might not necessarily be thought of as "quick"! This is also only taking months into account, so it's only looking at the month number and disregards the day.

If 2023 is your baseline:

Formulas are:

2023:

=IFERROR(IF(AND(YEAR(Start@row) = 2023, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2023, YEAR(End@row) = 2023), MONTH(End@row), IF(AND(YEAR(Start@row) < 2023, YEAR(End@row) >= 2023), 12, 0))), "")

2024:

=IFERROR(IF(AND(YEAR(Start@row) = 2024, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2024, YEAR(End@row) = 2024), MONTH(End@row), IF(AND(YEAR(Start@row) <= 2024, YEAR(End@row) >= 2024), 12, 0))), "")

2025:

=IFERROR(IF(AND(YEAR(Start@row) = 2025, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2025, YEAR(End@row) = 2025), MONTH(End@row), IF(AND(YEAR(Start@row) < 2025, YEAR(End@row) >= 2025), 12, 0))), "")

As you can see, if you were going further out then just change the year number to the relevant value.

This was a bit hurried, so may not be perfect for your purposes. Otherwise I'd probably recommend doing something with one of the functions similar to NETDAYS for the calculations. Hopefully it points you in the right direction though!

Scott Peters

Hi @Kelly Cepicky - Do you have any other specific criteria you can share, like column names, column type, and what you need the calculation to do?

Kelly Cepicky

If column "FTE Savings" has a number (any number), then I need "Monthly Savings" multiplied by "# of Months in the year" to give total savings for that year. If "FTE Savings" is zero, then leave blank. Hope that helps.

Nick Korna

Hi @Kelly Cepicky,

This should do what you're after:

=IF([FTE Savings]@row <> "", ([Monthly Savings]@row * [# of Months in the year]@row), "")

If you have a number in FTE Savings, but one/both of the other 2 columns is missing data then your Total Savings will show 0.

Sample data:

Hope this helps, but if you've any problems/questions then just post! 🙂

Kelly Cepicky

That formula works but..................

I have another problem. The FTE Savings will have occurred in either 2023, 2024 or 2025. How do I factor that into the calculation? There is a start date & a completion date & those can both be in different years. I need to be able to calculate the savings based on each year & how much of that savings falls into each year. I hope that makes sense. This would be so much easier on a call, lol.

Nick Korna

Does the amount of savings vary by year, or is it constant?

I am guessing you would like some additional columns for the breakdown of how much is saved per year?

Kelly Cepicky

actually your formula worked!!!!

Is there a simple way to calculate the number of months for each year that a row item has? For example, if in a row the item has a start date of 5/1/2023 & an end date of 4/30/2024, is there a quick formula that will calculate how many months are in each year?

Nick Korna

Totally possible, though the formula might not necessarily be thought of as "quick"! This is also only taking months into account, so it's only looking at the month number and disregards the day.

If 2023 is your baseline:

Formulas are:

2023:

=IFERROR(IF(AND(YEAR(Start@row) = 2023, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2023, YEAR(End@row) = 2023), MONTH(End@row), IF(AND(YEAR(Start@row) < 2023, YEAR(End@row) >= 2023), 12, 0))), "")

2024:

=IFERROR(IF(AND(YEAR(Start@row) = 2024, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2024, YEAR(End@row) = 2024), MONTH(End@row), IF(AND(YEAR(Start@row) <= 2024, YEAR(End@row) >= 2024), 12, 0))), "")

2025:

=IFERROR(IF(AND(YEAR(Start@row) = 2025, YEAR(Start@row) = YEAR(End@row)), MONTH(End@row) - MONTH(Start@row), IF(AND(YEAR(Start@row) < 2025, YEAR(End@row) = 2025), MONTH(End@row), IF(AND(YEAR(Start@row) < 2025, YEAR(End@row) >= 2025), 12, 0))), "")

As you can see, if you were going further out then just change the year number to the relevant value.

This was a bit hurried, so may not be perfect for your purposes. Otherwise I'd probably recommend doing something with one of the functions similar to NETDAYS for the calculations. Hopefully it points you in the right direction though!

Kelly Cepicky

Thank you!!!!

Nick Korna

No problem, glad to have helped. 🙂