Update values in the Single Select field based on a date provided

[Muhammad_Faheem]

Hi there AirTable Community,

I want values to update automatically based on the date provided. To go into little details, I want the “Production Week” column to change its fields, Week 1, Week 2, Week 3, and Week 4 based on the Kick-Off Date. So, if the kick-off date is Feb 15, 2022, and today is Feb 22, 2022, it should automatically update the value in the Production Week column to “Week 2”, and the in the next week, it should change its value to “Week 3” so on and so forth. I hope it makes sense.

Here is a quick 1-minute Loom Video of me demonstrating my concern: Loom | Free Screen & Video Recording Software

Thank you!

[Andy_Lin1]

Does it have to be a single select field? Otherwise, you could just use the formula
"Week "&(DATETIME_DIFF({Kick-Off Date},TODAY(),'weeks')+1)