Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 13, 2024 11:13 AM
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?
Sep 13, 2024 12:01 PM
Look at the WEEKNUM() function.
Sep 13, 2024 12:08 PM
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.
Sep 13, 2024 12:22 PM
Use two fields -- one for start week number, one for end week number.
Sep 13, 2024 12:50 PM
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
Sep 13, 2024 01:31 PM - edited Sep 13, 2024 01:31 PM
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.