Help

Counting manually entered dates in date range

1005 1
cancel
Showing results for 
Search instead for 
Did you mean: 
maximkolosov
4 - Data Explorer
4 - Data Explorer

Hello! Im totally newbie at Airtable and databases. Please help me to find out couple things. I am working on base for tracking crossfit gym membership.

Each client purchase membership for 30—40 days. Every membership has {Start Date} and {End Date}. End Date field is calculated with formula by adding days to {Start Date}. Сlient shows up during membership period (once a day). Every visit fills up with form to Visits table.

I have three tables:

  1. Clients — {Name}, {Email}, {Phone} etc
  2. Memberships — {MembershipID}, {Customer} (linked to Clients), {Type}, {Start Date}, {End Date}, {Ammount} etc
  3. Visits — {Date of visit}, {Visitors} (Multiple links to Clients)

Is there a way to take {Date of visit} and compare it with date range of active membership? Take all {Date of visit} and count how many of them lays between {Start Date} and {End Date} for every {MembershipID}?

Show me the way please or maybe I look at this completely wrong.

1 Reply 1
victor_alumanah
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi There, the solution below may be able to help, I have modified the visits table by adding the column membershpID to connect it to the membership table. I have then used rollup to bring in the start and end dates. The last colum calculates if the date of visit is in range. If you want you can now add a column to the membership tableusing the Rollup feature to bring back the sum of all visits in range from the “Visits” table. Hope this helps.
Victor

Gym
Gym2
Gym3