"metrics sheet" - showing First Day of Week from Week Number

HalfSun

For additional context, I am building on what I outlined here. Short version is that I have a sheet that is pulling in aggregated data to show changes over time.

I would like to show the first day of the week as well as the Week Number and have tried adding a column with this formula:

=DATE(2025, 8, 25) + (([Week Modified]@row - 1) * 7)

Date representing the start date of the project. And the [Week Modified] column showing which week number this data is for.

However, I keep getting an #INVALID COLUMN VALUE error. I have made sure my Week Modified column is formatted as a number. Is it because it can't tell the Week modified does refer to a week number? What am I missing?

I get the same error when I try to get it to give me the start date of the week the change was made using this formula:

=DATE(2025, 8, 25) + ((WEEKNUMBER([Status Change Date]@row - 1) * 7))

I also tried the solution from this post, adding a column for Year and modifying the formula so where it says Week@row, I have [Week Modified]@row…

=IF(WEEKDAY(DATE(Year@row , 1, 1) + [Week Modified]@row * 7) = 1, DATE(Year@row , 1, 1) + [Week Modified]@row * 7, (DATE(Year@row , 1, 1) + [Week Modified]@row * 7) - (WEEKDAY(DATE(Year@row , 1, 1) + [Week Modified]@row * 7) - 1))

HalfSun

UPDATE! I finally got it! On my data collection sheet, I added a "Week of:" column, and used this formula:

=[Date Column Name]@row + (2 - WEEKDAY([Date Column Name]@row )) - IF(WEEKDAY([Date Column Name]@row ) = 1, 7, 0)

Then on my metrics sheet added a Week of column as well, but that pulls an index of distinct week of dates from the data collection sheet using:

=INDEX(DISTINCT({Week of}), Row@row )