Hi!
I’m trying to sort my records by the years my project was active. Every record consists a project with different project durations. Some of the projects take less than a year, while others take over 5 years. In each record we fill in the start and end date. Now we would like to see how many active projects we had in each year. Does anyone know a simple solution for that? I’ve tried to use formulas for it, but that did not work out for me jet.
Thank you in advance!
A solution? Yes. Simple? Well…
Take a look at this demo base. It calculates during which calendar years a project was underway and expresses such periods of time in the format YEAR1|YEAR2|YEAR3|..YEARn|. The per-project time periods are then rolled up to a second table ([Calc]) through the technique of linking every record of the first table to a single record of the second. Within [Calc], various fields calculate the number of projects per-year (for years 2012 through 2018) or generate a single string showing projects {By Year}. (The calculations are performed based on a variant of @Simon_Brown’s algorithm to count the number of instances.)
Not too elegant, and the formulas have to be modified to hard-encode the upper- and lower-most years in the range, but it does work.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
