Comparing one date field with two or more dates (lookup)

[Tom_R]

Hi,

I’m trying to calculate if {Table 1 Field 2} (date field) is before {Table 2 Field 2} (rollup field to a date field).

The structure is:

Table 1.
Field 1: Student name
Field 2: Date student enrolled into college
Field 3: Subject name(s) (linked record to Table 2)
Field 4: Date(s) (rollup field through Field 3)

Table 2.
Field 1: Subject name
Field 2: date of class
Field 3: Student name

Basically, any formula cannot work around the rollup field because 1) it’s an array and (usually) has more than one date as there is more than one linked record… so I get something that looks like this: date, date, date, date.

Does anyone know how I might calculate if the Date student enrolled into college comes before Date of class, given that there might be more than one class?

Alternatively, creating a bridging table to capture both of these so that there is no use of a rollup that is showing more than one date.

Thanks for any help.
Tom.

[JonathanBowen]

Hi @Tom_R - are you trying to find out where the enrollment date comes before any class? If so your rollup of class start dates can be set to MIN(values)

This will give you a single, earliest date across all classes and this can be compared to the enrollment date:

My classes table is:

My formula in the last field is:

IF(DATETIME_DIFF({Date Rollup}, {Date of enrollment}, 'days') < 0, 'Yes')

JB

[Tom_R]

Hi @JonathanBowen,

Thanks for your detailed response and help.

To answer your question - not quite, and sorry if my initial post wasn’t clear, I’m trying to find if the enrolment date comes after any class. Which would just be a matter of switching the input values for the DATETIME_DIFF formula. But to make it a little more complicated, the class(es) would then need to be identified (by output), not just as a “yes”.

It looks like I’d be able to do your suggestion to find the earliest and latest dates for classes then compare them to the date of enrolment (using Min(values) and Max(values) in rollup fields), but what about those dates inbetween, as some students could have more than one class?

Appreciate your help, I just cannot seem to find way around this!

Tom.

[W_Vann_Hall]

Ah – so actually you’re comparing the date of enrollment against each class date, not vice-versa.

The answer is no: You can’t do that for an arbitrary number of dates, mainly because Airtable currently does not support looping processes.

—but you don’t have an arbitrary number of dates, do you? There is some maximum number of classes in which a student can enroll. All you need to do is create a big, ugly IF() statement and step your way through the list of start dates, comparing each to the enrollment date. If the date is before (or after; I don’t recall which you’re concerned about), spit out the date.

Or, rather, the class. That makes it a little harder.

What I would do is create another formula field in your [Classes] table called {ClassDate}. It would be configured with the following formula:

{Name}&
REPT(
    ' ',
    20-LEN(
        {Name}
        )
    )&
DATETIME_FORMAT(
    {Start Date},
    'DDMMYYYY'
    )

That will create a fixed-length field that looks something like this:

Computer Science    07012019

(The 20-character maximum class name length is arbitrary; if you offer a class called ‘Introduction to Etruscan Pottery,’ you’ll need to adjust the formula accordingly.)

In your [Students] table, change {Start Date (classes)} to rollup {ClassDate} instead of {Start Date}. Also create a count field called {NbrOfClasses} that counts the links from [Students] to [Classes].

Finally, create a formula field called something like {Already in Progress}; this will be used to list any classes where the start date falls before the student’s enrollment date. The formula for this field will be a multi-branching IF() statement designed to process up to and including the maximum number of possible classes. For the sake of this illustration, I’ll assume that maximum is 5:

IF(
    {NbrOfClasses}>=1,
    IF(
        IF_BEFORE(
            DATETIME_PARSE(
                MID(
                    {Start Date (classes)},
                    21,
                    8
                    ),
                'MMYYDDDD'
                ),
            {Enrollment Date}
            ),
        TRIM(
            MID(
                {Start Date (classes)},
                1,
                20
                ),
            )
        )
    )&
IF(
    {NbrOfClasses}>=2,
    IF(
        IF_BEFORE(
            DATETIME_PARSE(
                MID(
                    {Start Date (classes)},
                    51,
                    8
                    ),
                'MMYYDDDD'
                ),
            {Enrollment Date}
            ),
        ', '&
        TRIM(
            MID(
                {Start Date (classes)},
                31,
                20
                ),
            )
        )
    )&
IF(
    {NbrOfClasses}>=3,
    IF(
        IF_BEFORE(
            DATETIME_PARSE(
                MID(
                    {Start Date (classes)},
                    81,
                    8
                    ),
                'MMYYDDDD'
                ),
            {Enrollment Date}
            ),
        ', '&
        TRIM(
            MID(
                {Start Date (classes)},
                61,
                20
                ),
            )
        )
    )&
IF(
    {NbrOfClasses}>=4,
    IF(
        IF_BEFORE(
            DATETIME_PARSE(
                MID(
                    {Start Date (classes)},
                    111,
                    8
                    ),
                'MMYYDDDD'
                ),
            {Enrollment Date}
            ),
        ', '&
        TRIM(
            MID(
                {Start Date (classes)},
                91,
                20
                ),
            )
        )
    )&
IF(
    {NbrOfClasses}=5,
    IF(
        IF_BEFORE(
            DATETIME_PARSE(
                MID(
                    {Start Date (classes)},
                    141,
                    8
                    ),
                'MMYYDDDD'
                ),
            {Enrollment Date}
            ),
        ', '&
        TRIM(
            MID(
                {Start Date (classes)},
                121,
                20
                ),
            )
        )
    )&
IF(
    {NbrOfClasses}>5,
    ', ERROR! Too many classes!'
    )

(Obviously, if the student can enroll for, say, 100 classes, this becomes a very long formula. :winking_face: )

The result will be a comma-separated list of classes that met before the student enrolled.

The use of the {ClassDate} field is simply to simplify (!) the IF() statement by allowing one to parse fixed-length entries from the string. If for some reason you must support totally variable-length class names (that is, you have no way of knowing what the maximum class name length might be), you can still use this method — but the IF() statement becomes far more gnarly.

And, of course, if you have to support an entirely arbitrary maximum number of enrolled classes, then it [currently] can’t be done in Airtable…