Help

Date range to return seasonal week

Topic Labels: Dates & Timezones Formulas
912 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Kristen_Casselm
4 - Data Explorer
4 - Data Explorer

Hello my friends. I have a seasonal task calendar that is organized by project WEEKS. Our team works from Week 57 until Week 30. Tasks are entered into the Primary Column with an associated Start Date and End Date. Currently, the Project WEEKS are set up as tasks as well to allow the Project Managers to group by project week. When team members add a new task, they have to manually add the corresponding project week. I don't have to tell you what a chore that is. As the team Airtable person, I end up cleaning up the missing or erroneous data.

In the screenshot below, you can see that I've started experimenting hoping that it would lead me to a solution. Here's what I need in prose:

If the task START DATE and END DATE fall within the start and end date of a particular project week, return that project WEEK (currently in TEST Weeks). Do I need to reference the project weeks in another table? Thank you in advance for your help.

Screenshot 2023-02-22 at 8.18.59 AM.png

2 Replies 2
Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

A couple of recommendations:
- If you don't need times, I'd un-check "Include time" in the date columns.
- If there is a "Week" field (whether linked from another table or not), then you can group by week without having to add tasks:
Screen Shot 2023-02-22 at 12.50.13 PM.png

Questions:
- What if the start and end dates fall in different weeks?
- Do project weeks count backward? (You mentioned going from 57 to 30 rather than 30 to 57, and since there's more than 52, I'm guessing these don't related to calendar weeks.)
- Are there set date ranges for each week? (For example, is week 32 always from 01/31/2013 to 02/06/2013 ?) Or does the date ranges for a week change by project?

Kristen_Casselm
4 - Data Explorer
4 - Data Explorer
  • The time is needed for interview slots. I set up a date simplified field as I have been noodling over the solution.
  • We already group by Week in grid, gantt, and calendar views, but the task needs a week assigned to it to be included in the group. The team does not group by date range because the company speaks in terms of weeks to launch.

Answers:
- What if the start and end dates fall in different weeks? - It needs to return multiple project weeks
- Do project weeks count backward? (You mentioned going from 57 to 30 rather than 30 to 57, and since there's more than 52, I'm guessing these don't related to calendar weeks.) - The company aligns to weeks in a Seasonal Retail launch which spans 75 weeks. My team is counting down to a seasonal launch and works between weeks 57 to week 30 when we deliver our content. 
- Are there set date ranges for each week? (For example, is week 32 always from 01/31/2013 to 02/06/2013 ?) Or does the date ranges for a week change by project? - There are set ranges for each seasonal project week. You'll see them in my screenshot (specifically the Spring 2024 launch). In the Primary field the weeks are specific records named for the week (Week 42, Week 41, Week 40). Tasks within this project week can happen over the entire week, day(s), an hour, or bleed into the next week.

THANK YOU!