Sorting my records based on the active years

2057 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer


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!

1 Reply 1

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.