Google Sheets: Arrayformula to produce dates in reverse order

0
votes

This document is a template for invoicing for a voluntary group.

In the date column, I want to auto fill dates starting at the bottom so they appear in reverse order, with the most recent at the top. The start date is as shown at the bottom of the spreadsheet, and this could be any given Monday. Each row covers a whole week so each date will always be a Monday (ie. they increment in 7 day steps).

When I insert rows at the top of the data section (ie. below the titles) I need these to auto fill too with new dates.

I cannot work out how to do this and hope it is possible for you to help me. I was thinking perhaps an arrayformula, but as you can tell, my knowledge in this field in limited.

I don't mind if it needs to make use of hidden rows and/or columns to make it work, and I've been trying to avoid a script, preferring a formula.

Thank you for your help.

2
google-sheetsarray-formulasautomatic-updates
If you want row to be filled with the current date when you add data, you're going to need a script :/ - Liora Haydont

2 Answers

0
votes

B13: 

={"Week";"";"";SORT(B27+(ROW(B15:B25)-14)*7,1,0)}
  • ROW to create a sequence of numbers
  • SORT to sort dates in descending order.

If you insert a row between B15 and B16,new weeks will be created automatically.

0
votes

It's very simple, all you have to do is to set the formular =A9+1 at A8 cell and drag copy up if you add the new rows above.