# Current month checkbox formula

Options
✭✭✭✭✭✭

Hi,

Can anyone suggest me with a formula that will calculate the current month and check the box as soon as the first day of the month is reached? Layout of my sheet attached below.

Tags:

• ✭✭✭✭✭✭
Options

@Paul Newcome This worked! Thanks :)

• ✭✭✭✭✭✭
edited 05/11/20
Options

@Paul Newcome A quick follow up question - If I wanted the month gone by checked instead of the current month at all times, how will the formula look like? So basically although we are in May now, I would like the box for April to be checked and the same condition for future months and years.....

• ✭✭✭✭✭✭
Options

We would first generate the date of MONTH - 1 and YEAR and then use an IFERROR to generate MONTH = 12 and YEAR - 1 to account for January looking back to December.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)

Then we would take the MONTH and YEAR from the above for comparison the the MONTH and YEAR of the [Date Column]@row.

=IF(AND(MONTH([Date Column]@row) = MONTH(date_formula), YEAR([Date Column]@row) = YEAR(date_formula)), 1)

=IF(AND(MONTH([Date Column]@row) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), YEAR([Date Column]@row) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1))), 1)

• ✭✭✭✭✭✭
Options

@Paul Newcome Thanks for the breakdown and explanation. However, when I tried inserting it I get #UNPARSEABLE error. See attached formula that I used.

=IF(AND(MONTH([Date (Formula)]18@row) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), YEAR([Date (Formula)]18@row) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1))), 1)

• ✭✭✭✭✭✭
Options

It is because you are combining @row with a row number. You should be using one or the other, not both.

=IF(AND(MONTH([Date (Formula)]@row) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), YEAR([Date (Formula)]@row) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1))), 1)

or

=IF(AND(MONTH([Date (Formula)]18) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1)), YEAR([Date (Formula)]18) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 12, 1))), 1)

• ✭✭✭✭✭✭
Options

@Paul Newcome Still no luck with this formula. I tried inserting both, but now I get an incorrect arguement error. Could it be something else in the formula that's not right?

• ✭✭✭✭✭✭
Options

@Paul Newcome That finally worked! Thank you so much :)

• ✭✭✭✭✭✭
Options

Happy to help! 👍️