How to create an Original Start Date field from th...

[ana_rocadas]

I have a table with all our fiscal weeks, and their corresponding start and end dates.  ("Fiscal Weeks")

I have another table with projects, and the start date of each project. ("Projects")

I need to add a column to the Projects table that tells me which fiscal week the project belongs to, based on the start date of the project. 

Nothing I've tried seems to work. Any thoughts?

[kuovonne]

Nothing I've tried seems to work. Any thoughts?

---

What have you tried?

Here are some other ideas.

Link your [Projects] table and [Fiscal Weeks] table to each other with a linked record field. Run an automation with a "Find record" action to find the record for the fiscal week based on the start day of the project, and then updating the project record with a link to the fiscal week record.

Use the WEEKNUM() formula function to turn the start date into a week number.

Use a combination of WEEKDAY() and DATEADD() to convert your start date into the preceding Monday.

[ana_rocadas]

Thank you for your suggestions. I've tried linking both tables but can't seem to make the logic work. I've also tried an automation to update the project record with the fiscal week when a start date is added, but again, can't create a link between the tables there. I'm sure it's user ignorance. 

In excel this would be a formula such as IF(AND(A1<=start_date, start_date<=B1), "C", "") where Columns A and B show the beginning and end of each Fiscal Week and Column C shows the Fiscal Week #. Is there a way to translate this for Airtable?

A further complication is that our Fiscal Year begins Feb Wk 1, so it doesn't align with a calendar year. 

Any help is appreciated!