formula help

Jason Jordan

Hello I have a check box to auto check to indicate next month however; I've run into an issue with January not checking and I believe it has to do with it being a new year. I found a formula that someone needed to look back from January to December and i tried to make it work for next month but it's not working. How can I make it work for the new year so the checkbox is checked for January? Here is my current formula:

Basically I need a formula for a helper column to auto check the box for next month.

=IF(AND(MONTH([Vacation Start]@row) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 12, 1))), YEAR([Vacation Start]@row) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 12, 1)))), 1)

Thank you.

L_123

=IF(MONTH([Vacation Start]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 26) + 6), 1, 0)

Give that a try

L_123

Yes, this will work throughout the year.

Basically what it is doing is putting together a date that is the 26th of the current month and year. If you add 6 to that date, it is the 32nd. No matter the month, there is never 32 days in the year. So now you have a date in the next month.

Take the month from that calculated date, and compare it to the month of the date you want to, and return if they match or not.

Technically you could do this with many different numbers. For example you could say 28 and 4, or 27 and 10. as long as you pick numbers that when added together are greater than 32, and the second number is less than 28, you are good.

L_123

=IF(MONTH([Vacation Start]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 26) + 6), 1, 0)

Give that a try

Jason Jordan

That worked - will that continue to work moving forward so in January the check marks will come off January and move to February?

L_123

Yes, this will work throughout the year.

Basically what it is doing is putting together a date that is the 26th of the current month and year. If you add 6 to that date, it is the 32nd. No matter the month, there is never 32 days in the year. So now you have a date in the next month.

Take the month from that calculated date, and compare it to the month of the date you want to, and return if they match or not.

Technically you could do this with many different numbers. For example you could say 28 and 4, or 27 and 10. as long as you pick numbers that when added together are greater than 32, and the second number is less than 28, you are good.

Jason Jordan

thank you for the explanation that makes perfect sense now.