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.

ShannonL

Hey all. I have a similar situation I need help with.
I have text/number columns that represent quarters of the year dating back to 2018 (i.e. 23Q1, 23Q2). I want to be able to sum the cost across the quarters in each row, before today's date and subtract that from the total [Planned Billing] to find what's remaining. Each quarterly column has a different dollar amount in the cell. For example, the rows in the [Remaining Billing] column should change every quarter based on the current date ([Planned Billing] minus the sum of all quarters before today's date in each row). Attached is a screen shot of the sheet. Any help is much appreciated.

Screenshot 2024-06-21 143627.png

Paul Newcome

@ShannonL Wouldn't [Remaining Billing] just be this?

=[Planned Billing]@row - [Billed To Date]@row

ShannonL

@Paul Newcome Let me give you some context. [Billed to date] is not correct as it currently is shown. The formula there is a simple "=sum(23Q1:23Q4)" which doesn't give me the result I'm looking for. So my thought was either to create to formula as mentioned above in the [Remaining Billing] Column or the [Billed to date] column. If the formula is used in [Billed to Date], then yes, i could use the formula you mentioned to calculate [Remaining Billing]. The issue I still have is summing the cost across the quarters based on todays date. So for this exercise, pretend [Billed to Date] does not exist. I may end up deleting that column. Sorry for the confusion.

Thanks