Current month checkbox formula

sahilhq

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.

Capture.PNG

Paul Newcome

Try something like this...

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

This will leave the box checked for the entire month. Since we are only checking month and year number in the AND statement, it won't matter what day it is.

Paul Newcome

I haven't quite figured out exactly where, but it looks like the parenthesis are off. You should only have 1 at the very end. Try copy/pasting this formula

=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)

Paul Newcome

Try something like this...

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

This will leave the box checked for the entire month. Since we are only checking month and year number in the AND statement, it won't matter what day it is.

sahilhq

@Paul Newcome This worked! Thanks :)

sahilhq

@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.....

Paul Newcome

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)

sahilhq

@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)

formula.PNG

Paul Newcome

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)

sahilhq

@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?

Capture.PNG

Paul Newcome

I haven't quite figured out exactly where, but it looks like the parenthesis are off. You should only have 1 at the very end. Try copy/pasting this formula

=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)

sahilhq

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

Paul Newcome

Happy to help! 👍️