How to compare a date in a field with several dates in a lookup field

[Fabrizio_Latini]

Hi,
I need a formula that list (comma separated values) in a third field that exclude from a lookup field (in which there are comma separated dates) dates before the date in the first field.

Example:

Field1 “Join date” = 01 oct 2018.

Field2 “lessons” (is a lookup field) = 15 sep 2018, 15 oct 2018, 15 nov 2018.

Field3 “possible lessons” FORMULA RESULTS = 15 oct 2018, 15 nov 2018.

Thank you!

[Elias_Gomez_Sai]

What is your base structure? Maybe could be easier to compare dates in other table or approach.

[Fabrizio_Latini]

TABLE “members”
Field Name
Field Date of Join (example: 01 oct 2018)
Field LOOKUP to TABLE “lessons” / field “Date of Lesson” (example: 15 sep 2018, 15 oct 2018, 15 nov 2018)* *: comma separated values lookup from table “lesson” below

TABLE “lessons”
Field “Date of Lesson” (record1: 15 sep 2018; record2: 15 oct 2018; record3: 15 nov 2018)
Field “Attendants” LINK to “members”

When I want to count the total of registered lessons it is simple (I do a rollup field from table “lessons” to a rollup field in “members”)

Now I want to show how many lessons EACH member should have done, to do a percentage (attendances/maximum lessons * 100)

BUT each member could join in a different date so the maximum number of lessons for each member is different.

Thank You

[Elias_Gomez_Sai]

Maybe there is a fancy Formula like other advanced users use to post, but I think the best approach is to have a junction table for Attendances.

• you create all combinations for 1 member and all lessons
• create a Formula field to output the availability of the lessons for that Member
• in the Members table you could count the available lessons with a Rollup
• also add an Attended checkbox field to mark if the Member was in the Lesson
• add a Rollup to count the Attended lessos
• add a Formula field to do the percentage
• for a new Member, create it, duplicate the Attendances of another Member, reset the Member and Attended fields, and set that new Member (I’d use Groups for easy manipulation). Or even you could have an “empty” Member to duplicate from :thinking:

Even you could create a Available and Past field, to calculate the percentage of Lessons attended that have been performed.