Current month checkbox formula
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.
Best Answers
-
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.
-
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)
Answers
-
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 This worked! Thanks :)
-
@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.....
-
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)
-
@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)
-
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)
-
@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?
-
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 That finally worked! Thank you so much :)
-
