I am making a workout/habits tracking app and want to use it daily, however, at the end of the month I want a new month to appear and the last month to be hidden. Can I do that and how?
Have a column in your data that would determine whether the date in each row is this month or last month. You can filter víibility based on that column.
Can that be done automatically each month, or do I need to do that manually on the 1st day of the month?
One idea would be to have 31 rows (one for each day in a month), Then build the date column using formulas in the sheet. You could build a base date that figures out the current month and year, but sets the day as the first of the month. Then for the following rows, just take the prior row date plus 1. You could get a lot more technical and build the formula so it’s a constant rolling date and increments to the next month once the day is past, or you could simply let it refresh all of the dates once a month.
Unless you are building out a list for every day of the year and want to keep old data intact without seeing it, then an arrayformula that checks if the date’s month is the current month should work. You can have it return a true/false that can be used to filter the calendar list.
1 Like
You can use this arrayformula to get what I said above. Put this in the first cell of one new column, and change the A2:A part to the column where you are storing the dates.
={"By month";ARRAYFORMULA(IF(MONTH(A2:A)=MONTH(TODAY()),"This month",IF(MONTH(A2:A)=MONTH(EDATE(TODAY(),-1)),"Last month","")))}
1 Like