Why is a formula not triggering unless someone opens the sheet?
I have 150 sheets that individuals use as time sheets. The sheet has rows for all the dates for a quarter. I have a "Current Month?" check box column in the sheet that checks the cell if the row date is in the current month. It uses this formula:
=IF(MONTH(Date@row) = MONTH(TODAY()), 1)
I have another check box column called "Previous Month?" that is checked if the row date is in the month prior. It uses this formula:
=IF(MONTH(Date@row) = MONTH(TODAY(-30)), 1)
I use these two hidden columns to filter two reports, one for the current month and one for the previous month.
Users are supposed to work out of these two reports rather than the sheet. However, when January turned to February, the check boxes did not switch until someone opened the sheet. So, if I haven't opened the sheet yet and it's February 3, the Current Month report still shows January dates.
Is there a way around requiring someone to open the sheet for the check boxes to switch automatically? Would a workflow be better instead of the formula? I'm trying to avoid having 150 users have to open the sheets at the beginning of the month just so the check boxes update. They should focus on the reports.
Help Article Resources
Check out the Formula Handbook template!