Extrapolating Week Number from Dates

[ivy]

Hi! On my table I have three fields, "Start Date", "End Date", and "Week Number". I am trying to make an automation that extrapolates the week number from the date range. I know there is a formula that does this but I'm looking to have the field be a single-select color-coded field. I have an automation that is working out well but I can't figure out how to do the automation so that it would automate multiple weeks if the start and end date spans multiple weeks. 

Current automation: 

If start date is 1/1/25 and end date is 1/3/25, return week 1. 

Where I want to get: 

If start date is 1/1/25 and end date is 1/9/25, return week 1 and week 2. 

Is there an easy way to do this? 

[Wyatt_Barnett]

Look at the WEEKNUM() function.

[ivy]

I did but it only lets me pick one date to extrapolate week number for. It doesn't allow me to select both start date and end date so it doesn't solve the issue of extrapolating week numbers from start and end dates that spans multiple weeks. 

[Wyatt_Barnett]

Use two fields -- one for start week number, one for end week number. 

[ivy]

I've tried that but it doesn't show the weeks in between - e.g if the dates spans 4 weeks this method doesn't give me the weeks in between, only the week the start and end date is on and even if this did work, i would rather this all be in one field 

[Wyatt_Barnett]

Sorry -- I didn't quite catch you wanted a list of week numbers above.

I would probably do this in a script block in an automation. You should be able to pass in two dates and get a list of week numbers out of the other side with a little javascript.