Modifying a date based on 2 variables

Carroll Wall

Hello Everyone!

I would like to create a formula that says

If the value in the "Variable" column is "HIGH" AND the date in "Column 1" has a year less than today's year, return that date with TODAY"S year. Otherwise return that date unchanged.

In other words, dates in the previous years are brought to this year, dates in this year or future years remain unchanged.

The result would look like this (with "Goal Column" being where the formula is)

I have gotten this far:

IF(AND([Variable]@row = "High", magic happens here to look at just the year in First Column). DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row, [First Column]@row)))

(I know I am missing some parentheses in the example, I can fix those. I am looking for the magic sauce in the middle)

Thank you very much for taking a look.

Carson Penticuff

Sure, here is how it breaks down:

=IF([Variable]@row <> "High", ""

<> is a way of saying "not equal to". So, in this case, we are saying "Do the following thing if [Variable] is not equal to "High". The double quotes, "", is another way of saying "blank". So if our formula sees that [Variable] contains anything other than "High", it is going to set [Goal Column] as blank.

IF(YEAR(TODAY()) > YEAR([First Column]@row), DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)), [First Column]@row))

This section of the formula applies to the "else" part of the original IF statement. The first part looked for [Variable] not equal to "High", so this part will apply to [Variable] that does equal "High". First, we are asking whether today's year, YEAR(TODAY(), is greater than the year of [First Column], YEAR([First Column]). If it is, we are going to build a date using the present year, combined with the month and day from [First Column], DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)).

[First Column]@row

Lastly, if the current year is not greater than [First Column], i.e., they are the same, then we are simply going to set [Goal Column] to the same value as [First Column].

Carson Penticuff

=IF(AND([Variable]@row = "High", YEAR(TODAY()) = IFERROR(YEAR([First Column]@row), 0) + 1), DATE(YEAR([First Column]@row) + 1, MONTH([First Column]@row), DAY([First Column]@row)), "")

This should take care of it. I included an IFERROR to keep things from getting ugly in the event someone forgets to enter a date in [First Column].

Carroll Wall

Hi Carson, thank you for taking the time to help.

I put this in my sheet and am getting and #INCORRECT ARGUMENT error. Maybe too many references back to First Column is confusing it?

As a side note, I would not need the IFERROR as the date in "First Column" column is autogenerated and will not ever be blank.

Much obliged,

C

Carson Penticuff

I have it working in a test sheet of my own and copy/pasted the formula back out of my previous comment to be certain there wasn't a typo and it still works. Are [First Column] and [Goal Column] both formatted as dates? I don't believe an error there would throw this specific error, but it never hurts to check.

Carroll Wall

Thank you for the speedy reply!

Yes, the columns are formatted as DATE.

I copy/pasted your formula into my sheet and corrected for column names.

I no longer get the INVALID ARGUMENT error, but now I get no returns at all on any row.

THanks,

C

Carson Penticuff

I looked back over your initial question and I definitely misinterpreted what you were asking. Try this:

=IF([Variable]@row <> "High", "", IF(YEAR(TODAY()) > YEAR([First Column]@row), DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)), [First Column]@row))

Carroll Wall

Would you be able to write a narrative of what your new formula is doing?

I am looking for 2 things:

1) Look for variable "High"

2) Then check the year and correct to current year only if it is the past, otherwise leave it unchanged

I don't see an "AND" in your formula so I am not sure both items have been accounted for.

To add context, I will have a separate column where I will be doing the same manipulation of the date when the variable is not "High". I was planning on using the same formula (Once I got it worked out) with the first term changed to [Variable]@row <> "High" to accomplish this.

Carson Penticuff

Sure, here is how it breaks down:

=IF([Variable]@row <> "High", ""

<> is a way of saying "not equal to". So, in this case, we are saying "Do the following thing if [Variable] is not equal to "High". The double quotes, "", is another way of saying "blank". So if our formula sees that [Variable] contains anything other than "High", it is going to set [Goal Column] as blank.

IF(YEAR(TODAY()) > YEAR([First Column]@row), DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)), [First Column]@row))

This section of the formula applies to the "else" part of the original IF statement. The first part looked for [Variable] not equal to "High", so this part will apply to [Variable] that does equal "High". First, we are asking whether today's year, YEAR(TODAY(), is greater than the year of [First Column], YEAR([First Column]). If it is, we are going to build a date using the present year, combined with the month and day from [First Column], DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)).

[First Column]@row

Lastly, if the current year is not greater than [First Column], i.e., they are the same, then we are simply going to set [Goal Column] to the same value as [First Column].

Carroll Wall

All right, great! It seems to be working! YAY!

AND....

For my other column where I want to do the same DATE manipulation where the Variable is either Low or Medium, I changed the first term to =IF[Variable]@row="HIGH", "" , and it is working as desired there.

I very much appreciate your time and patience to explain this.

Have a great rest of your day and may all your formulas resolve on the first try!

Carroll

Carson Penticuff

I'm glad I could help!

👍️