Google Sheets. Automatically change given date plus n days-CodePudding

Good afternoon. I have 2 cells for 2 dates with date format on them and formula =DATE(2022; 1; 19). One cell is a first day of a current week period. Another cell is first day of a next week (=B17 7). I want to make both those cells to automatically change 7 days every week after i enter for example the first current date.

CodePudding user response：

You can use for the first day (monday)

=today()-WEEKDAY(today(),2) 1

CodePudding user response：

One way of solving your need while escaping circular dependency error is to do the following:

Keep cell B17 as is (=B13 7), however, instead of putting a date in cell B13, just put the following formula :

=TODAY()-WEEKDAY(TODAY(),2) 1

The result would be that the 2 cells will always update to the correct dates every time you'd open the spreadsheet.