Using dates in a formula for text/number columns

J Anzalone

I have text/number columns that represent weeks in a project timeline template. I want to be able to keep the column names generic (Week 1, Week 2, etc) and have the Monday date of each week in Row 1. Projects last months to over a year, so manually typing these dates in is tedious. The ideal scenario would be to put in the Monday corresponding to Week 1 then have a formula in the remaining Week # columns to count forward. However, when I set this up, this is what happens. The formula in Week 2 is (=[Week 1]@row + 7)

Week 1 Week 2

1/1/2024 1/1/20247

I can't format the columns to be dates because the data in the rows below are not dates. Is there a formula out there that can read the date format or a date format that I can use where the text/number columns would understand what I'm trying to do?

My short term solution is to set this up in an external software where the aforementioned formula works and copy and paste the dates in. I'd like to be able to keep it all in one sheet.

Carson Penticuff

Would it be possible to provide a screenshot with anything sensitive removed?

JamesB

J Anzalone

@JamesB that works until the month should change.

@Carson Penticuff here's a screenshot:

I want to put the first Monday date in Week 1 row 1, then have the rest of the Week # row 1 fill in. The sheet goes out over 52 weeks. Week 1 will not always be the first of the year.

JamesB

@J Anzalone

To solution for this, create a helper column that is also a Date Column. in the Helper use the formula =[Monday]@row + 7 Then you can just reference the helper column in your Task/Number column =[Helper]

Paul Newcome

You will need a date type column to kick things off which would contain your starting date there in row 1. It would be immediately to the left of the [Week 1] column and can be hidden after setting up. Then the formula in the [Week 1] column (which can then be drag-filled across the rest of the columns) would look like this:

=$[Starting Date]@row + ((COUNTIFS($[Starting Date]@row:[Starting Date]@row, @cell <> "") - 1) * 7)

The key is the $ to lock in the first and second references to the [Starting Date] column and leaving the third reference unlocked.