Create a counter based on dates

Hi,
I’m trying to manage sprint over a Coda Doc.
I’ve been trying to create an automated counter that will represent the sprint my team are in based on the week the date the task was created on.

I’ve created a column that represents the date the task was created on (with a formula that automatically fills in the current date) and now on a different column I want to have a counter that increase the number each week.

for example - if a task was created somewhere between the 20-26 of November it will count it as sprint 1 - a week after, sprint 2 and so on…

thanks!

Hi @Jonathan_Weber and Welcome to the Community :partying_face: !

I think creating a dedicated Sprint table (where you would store the start date and end date of each sprint) and link that table through a lookup to your Tasks table might potentially be something you could try here :blush:

This is what I did in the sample you’ll find below :innocent:

Each “task” in the Tasks table is automatically linked to a specific sprint from the Sprints table by comparing their start and end dates to the date in thisRow.Date :blush:

The whole Filter() formula is this one :

Sprints.Filter(Start <= thisRow.Date AND End >= thisRow.Date)

I know this is not exactly what you wanted but it might allow you more on the long term :blush:

1 Like

Amazing! thank you very much :muscle:

1 Like

No problem @Jonathan_Weber :wink: !

Always glad to help if I can :blush: !

2 Likes

Just used your solution and it works perfectly - a quick question tho:
even tho i colored the sprints table like you did, the sprints lookup in the tasks table is not in color… do you know how I solve this?
thanks!

Hmmm :thinking: … In the lookup field settings there’s an Apply styling options (which you’ll find in Lookup optionsOptions SettingsApply styling) which allows you to pass along the conditional formatting of the source table (if toggled On) or not (if toggled Off).

The thing here is that it’s toggled On by default.

So unless you toggled that setting Off when you added the lookup field to your Tasks table, the conditional formatting should already be there :blush: … Or you used another type of field to store the references to your sprints from the Sprints table (I.e.; a Text field) and in that case it won’t work (only lookup fields have the ability to pass along the conditional formatting )

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.