Just wanted to share an awesome exchange I had with the Airtable Support Staff!
We have programs with different start dates that meet on different days. I wanted to count the total days since start dates that have passed so that we can have our Attendance System Account for it. This is what they said:
Do you have a field in your base that indicates whether a role meets Tuesday/Fridays or Mon/Wed/Fri? If so, you could combine the formula that Kuvonne suggested in the community post you linked with a IF( statement to calculate the number of class instances that have occurred.
In the example below, I created a formula that operates under the assumption that there is a field denoting ‘T/F’ for the Tuesday-Friday class and ‘M/W/F’ for the 3 day meeting schedule.
If you have a Start and End date that you will be comparing, you could structure your formula like the below:
IF({Meeting Cadence}=‘T/F’, SUM(DATETIME_DIFF({End Date}, {Start Date}, ‘weeks’)+ IF(WEEKDAY({End Date}) >=
WEEKDAY({Start Date}), AND(WEEKDAY({End Date}) >= 2,WEEKDAY({Start Date}) <= 2),
OR(WEEKDAY({End Date}) >= 2,WEEKDAY({Start Date}) <= 2)),DATETIME_DIFF({End Date}, {Start Date}, ‘weeks’)+
IF(WEEKDAY({End Date}) >= WEEKDAY({Start Date}), AND(WEEKDAY({End Date}) >= 5,WEEKDAY({Start Date}) <= 5),
OR(WEEKDAY({End Date}) >= 5,WEEKDAY({Start Date}) <= 5))),
IF({Meeting Cadence}=‘M/W/F’, SUM(DATETIME_DIFF({End Date}, {Start Date}, ‘weeks’)+ IF(WEEKDAY({End Date}) >=
WEEKDAY({Start Date}), AND(WEEKDAY({End Date}) >= 1,WEEKDAY({Start Date}) <= 1), OR(WEEKDAY({End Date}) >= 1,
WEEKDAY({Start Date}) <= 1)), DATETIME_DIFF({End Date}, {Start Date}, ‘weeks’)+ IF(WEEKDAY({End Date}) >= WEEKDAY({Start Date}),
AND(WEEKDAY({End Date}) >= 3,WEEKDAY({Start Date}) <= 3), OR(WEEKDAY({End Date}) >= 3,WEEKDAY({Start Date}) <= 3)),
DATETIME_DIFF({End Date}, {Start Date}, ‘weeks’)+ IF(WEEKDAY({End Date}) >= WEEKDAY({Start Date}), AND(WEEKDAY({End Date}) >= 5,
WEEKDAY({Start Date}) <= 5), OR(WEEKDAY({End Date}) >= 5,WEEKDAY({Start Date}) <= 5)))))
If you are not utilizing an end date field and would instead like to calculate the number of meetings that have occurred between the start date and the current date, you could utilize NOW( ) instead of an End Date field in that formula like this:
IF({Meeting Cadence}=‘T/F’, SUM(DATETIME_DIFF(NOW(), {Start Date}, ‘weeks’)+ IF(WEEKDAY(NOW()) >=
WEEKDAY({Start Date}), AND(WEEKDAY(NOW()) >= 2,WEEKDAY({Start Date}) <= 2),
OR(WEEKDAY(NOW()) >= 2,WEEKDAY({Start Date}) <= 2)),DATETIME_DIFF(NOW(), {Start Date}, ‘weeks’)+
IF(WEEKDAY(NOW()) >= WEEKDAY({Start Date}), AND(WEEKDAY(NOW()) >= 5,WEEKDAY({Start Date}) <= 5),
OR(WEEKDAY(NOW()) >= 5,WEEKDAY({Start Date}) <= 5))),
IF({Meeting Cadence}=‘M/W/F’, SUM(DATETIME_DIFF(NOW(), {Start Date}, ‘weeks’)+ IF(WEEKDAY(NOW()) >=
WEEKDAY({Start Date}), AND(WEEKDAY(NOW()) >= 1,WEEKDAY({Start Date}) <= 1), OR(WEEKDAY(NOW()) >= 1,
WEEKDAY({Start Date}) <= 1)), DATETIME_DIFF(NOW(), {Start Date}, ‘weeks’)+ IF(WEEKDAY(NOW()) >= WEEKDAY({Start Date}),
AND(WEEKDAY(NOW()) >= 3,WEEKDAY({Start Date}) <= 3), OR(WEEKDAY(NOW()) >= 3,WEEKDAY({Start Date}) <= 3)),
DATETIME_DIFF(NOW(), {Start Date}, ‘weeks’)+ IF(WEEKDAY(NOW()) >= WEEKDAY({Start Date}), AND(WEEKDAY(NOW()) >= 5,
WEEKDAY({Start Date}) <= 5), OR(WEEKDAY(NOW()) >= 5,WEEKDAY({Start Date}) <= 5)))))
If a class is marked as ‘T/F’, this will report how many times it should have met between the start date and today.
