Calculate which weeks have been between two dates

[Tom_Sidebrant]

In order to track the workload we’ve had in the past, we want to create a formula that displays which specific weeks we worked on different projects. That way, we’ll be able to see how many projects we had active during specific weeks. We know which week we started a project, and which week we finished it, but I can’t figure out how to display the weeks that passed in between those dates.
The number of weeks in between isn’t relevant, we want to see “We worked on 45 different projects during week 14”.

Any ideas?

[Kamille_Parks]

Create a new formula field called {Weeks} with this as the formula:

"1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53"

(Its just a text string that lists all possible week numbers: 1-53)

Set your {Weeks active} field to be a formula-type field with this formula:

MID(
   Weeks, 
   FIND({Week - Created}, Weeks), 
   FIND({Week - Finished}, Weeks) + LEN({Week - Finished}&"") - FIND({Week - Created}, Weeks)
)

Note: This won’t work for projects than start at the end of the year (i.e. week 50 of 2021) and finish at the beginning of the next year (i.e. week 1 of 2022).