Weeks in between two dates

Showcase Tips and Hacks
1

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])))

5 Likes
2

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
3

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
4

FYI - I think this breaks when passing the year threshold.

Screen Recording 2020-06-02 at 02.55 PM
Screen Recording 2020-06-02 at 02.55 PM696×508 247 KB

5

Would be Fixed with something like this.

if(Year(thisRow.[End date])==Year(Now()),Sequence(thisRow.Startdate.WeekNumber(),thisRow.[End date].WeekNumber()),Sequence(thisRow.Startdate.WeekNumber(),Date(Year(Now()),12,23).WeekNumber())+Sequence(Date(Year(thisRow.[End date]),1,1).WeekNumber(),thisRow.[End date].WeekNumber()))

2 Likes
6

Thanks for the help here, here’s what I have done to get this to work over the year threshold:

Sequence(0, ((Year(endDate)-Year(startDate))*52)+endDate.WeekNumber() - startDate.WeekNumber() ).FormulaMap( startDate + Days(7*CurrentValue))

This would be much easier if relativeDate() worked for weeks as well as months.