Better formula for populating Month and Week when preparing bulk entries

Google Sheets Show & Tell
1

Tiller provides recommended formulas for automatically populating the correct month and week dates when preparing bulk imports to paste into the Foundation Template (I’d link to them here, but I’m prohibited from including links).

I have tweaked those formulas slightly to be cleaner. Where the original formulas will continue to add dates into empty rows, the below do not. And they’re shorter, and who doesn’t like shorter formulas :slight_smile:

Month
=arrayformula(if(row(B1:B)=1,"Month",if(B1:B<>"",date(year(B1:B),month(B1:B),1),"")))
Week
=arrayformula(if(row(B1:B)=1,"Week",if(B1:B<>"",B1:B-WEEKDAY(B1:B)+1,"")))

Enjoy

1 Like
2

Thanks for this! I don’t have a need for it yet but I do love shorter formulas!

3

Thanks so much for this @edwin. I just updated the help articles to include those new formulas.

The only edit I made was to the column name. Since Tiller automatically populates Month and Week columns so reusing those header keywords can cause problems for folks who put these formulas in their actual Transactions sheet vs only a manual import prep sheet.

1 Like