Get First Date of Week in Google Sheets, When the Week of the Year is Related to the Row Number

*phew* what a title

For a recent project, I wanted to set up a Google Sheet that tracked something daily/weekly. I wanted one column to show the starting day of the week to help keep me on track, and I ended up with this:

I used ChatGPT to help me make the formula to determine the first day of the week, indexed to the current row:

=DATE(YEAR(TODAY()), MONTH(DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1), DAY(DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1))

This is how ChatGPT explains how the function works:

This Google Sheets function returns the date that corresponds to the first day of the week for a given row number. The row number is specified by the ROW() function, which returns the row number of the current cell.

The function uses the following steps to calculate the date:

1. It uses the YEAR(TODAY()) function to get the current year.
2. It uses the DATE(YEAR(TODAY()),1,1) function to get the first day of the current year (e.g. January 1).
3. It uses the WEEKDAY(DATE(YEAR(TODAY()),1,1)) function to get the day of the week of the first day of the year as a number (e.g. 1 for Monday, 2 for Tuesday, etc.).
4. It uses the (ROW()-2)*7 expression to calculate the number of days to skip from the first day of the year to get to the desired week. For example, if the ROW() function returns 2, then the expression will evaluate to 0, which means that no days need to be skipped. If the ROW() function returns 3, then the expression will evaluate to 7, which means that one week needs to be skipped.
5. It uses the DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1 expression to calculate the date that corresponds to the first day of the week for the given row number.
6. It uses the MONTH(DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1) function to get the month of the calculated date.
7. It uses the DAY(DATE(YEAR(TODAY()),1,1)+(ROW()-2)*7-WEEKDAY(DATE(YEAR(TODAY()),1,1))+1) function to get the day of the month of the calculated date.
8. It uses the DATE() function to combine the year, month, and day into a single date.

For example, if the ROW() function returns 2 and the current year is 2022, the function will return the date January 3, 2022. If the ROW() function returns 3 and the current year is 2022, the function will return the date January 10, 2022.

It took a lot of trial and error, and manual adjustments to get the function to work, and it was really fun to play with ChatGPT and work together to get this working. While I could have trial and errored myself to the end functionality eventually, this did end up saving me quite a bit of time.

If you, random Internet stranger, want to use this function yourself, but your rows aren’t lined up the same as mine, you’ll just need to adjust the (ROW()-2) type areas to change the row offset you’re currently starting at.