one thing I always do with the Arrayformula is to hide it in plain sight. By losing the very first row of data made it so hard to manage the Sheet. So I hoped this idea would help everyone enjoying more from the formula benefit.
Here is how the implementation goes
Here is how the full formula goes in column C, to find the MON-FRI of the specific week for example:
=ARRAYFORMULA(IF(B:B=“timer”,“week fx”,IF(B:B="","",text(DATE(YEAR(B:B),1,1)-(WEEKDAY(DATE(YEAR(B:B),1,1))-2)+(WEEKNUM(B:B)-1)*7+7,“d-”) &TEXT(DATE(YEAR(B:B),1,1)-(WEEKDAY(DATE(YEAR(B:B),1,1))-1)+(WEEKNUM(B:B)-1)*7+12,“d”&TEXT(DATE(YEAR(B:B),1,1)-(WEEKDAY(DATE(YEAR(B:B),1,1))-1)+(WEEKNUM(B:B)-1)*7+12," mmmm")))))
The structures are:
- Put the formula inside the header column cell
- Start with first IF() checking if it is the header row by checking the leftmost row if it is the Row ID (where i always put it there, or in this case my leftmost row header is ‘timer’, if so then just show the column title for this one ‘week fx’ (fx refers to “this one have the formula in it don’t accidentally delete it”).
- Next IF() is for checking if the most important cell in the row is empty. Means that row supposed to be emptied, so show nothing. But if not, then start showing something according to my formula.
The best part is Glide won’t be affected by the formula inside header column. So you can keep the formula even it is the sheet you may have to add the rows in all the time. The sheet also can keep the additional formulated data alongside the app’s data harmlessly.