Sorting my records based on the active years

[BramT]

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!

[W_Vann_Hall]

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.

Airtable

Project by Year demo - Airtable

Explore the "Project by Year demo" base on Airtable.