Weeks in between two dates

Was asked this question so sharing in case it helps others

Our current workflow is built around week numbers. I was wondering if there is a way of creating a column that would build a list of Week Numbers based on a date range? i.e. List the week numbers between a Start Date and End Date value. Also our weekday starts on Monday

To do this I used Sequence to get the weeks in between the start and end date and then leveraged @Daniel_Stieber solution First day of the week Monday/Sunday

Formulas used

Weeks In Between - Sequence(thisRow.[Start Date].WeekNumber(),thisRow.[End Date].WeekNumber() )

Start Date if WeekDay Starts on Monday - if(WeekdayName(thisRow.[Start Date])=“Sunday”,WeekNumber(thisRow.[Start Date])-1,WeekNumber(thisRow.[Start Date]))

Weeks in Between ( Monday Start of week) -

Sequence(if(WeekdayName([Start Date])=“Sunday”,WeekNumber([Start Date])-1,WeekNumber([Start Date])),if(WeekdayName([End Date])=“Sunday”,WeekNumber([End Date])-1,WeekNumber([End Date])))

3 Likes

Nice work @mallika :raised_hands: and great original solution from @Daniel_Stieber :grinning:

One of our teams (different Coda project) start their sprint weeks on a Tuesday. Can anyone advise how I would go about adding a additional day to the function i.e. deduct a weeknumber if the week starts/ends on a Sunday or Monday.

I was thinking to combine a sequence function with switchif but my first attempt failed. This one isn’t urgent so I’ll come back to it but would be great to hear if I’m on the right lines.

1 Like

Hey @Harry_Harrison,
I think you can just extend it like this:

Start Date if WeekDay Starts on Tuesday - if(WeekdayName(thisRow.[Start Date])=“Sunday” OR WeekdayName(thisRow.[Start Date]) = “Monday”,WeekNumber(thisRow.[Start Date])-1,WeekNumber(thisRow.[Start Date]))

Weeks in Between ( Tuesday Start of week) -
Sequence((if(WeekdayName(thisRow.[Start Date])=“Sunday” OR WeekdayName(thisRow.[Start Date]) = “Monday”,WeekNumber(thisRow.[Start Date])-1,WeekNumber(thisRow.[Start Date]))),(if(WeekdayName(thisRow.[End Date])=“Sunday” OR WeekdayName(thisRow.[End Date]) = “Monday”,WeekNumber(thisRow.[End Date])-1,WeekNumber(thisRow.[End Date]))))

1 Like