Calculate Number of Class Sessions between a Start and End Date

aschneiderheinze1025

I've tried many variations of formulas, but I am getting #INCORRECT ARGUMENT SET every time.

[Session (Days)]: Dropdown box, multiselect, days of week (e.g., "Tuesday", "Monday and Wednesday")

Session Meeting (Start Date): Date column, enter start date of course (e.g., 2/11/2025)

Session Meeting (End Date): Date column, enter end date of course (e.g., 5/30/2025)

I want to calculate how many actual session meetings there are. For example, if they choose "Monday and Wednesday", I want the formula to count how many Mondays and Wednesday are on the calendar between (and including) the Start and End dates. (Note: [Session (Days)] can include Saturday and Sunday, so the formula should not exclude weekends.)

EXAMPLE: Sessions are on Tuesdays, between January 7, 2025, and April 8, 2025. There are 13 Tuesdays between (and including) those dates.

Thanks for any suggestions!

jessica.smith

If you don't mind adding some helper columns this should work for you…

Add the following columns with the associated column formulas:

Total Sundays

=IF(ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) = 1, IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) = 1, WEEKDAY([Session Meeting (End Date)]@row) >= 1), 1, 0), IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) > 1, WEEKDAY([Session Meeting (End Date)]@row) < 1), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 2, IF(OR(WEEKDAY([Session Meeting (Start Date)]@row) > 1, WEEKDAY([Session Meeting (End Date)]@row) < 1), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 1, ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7))))

Total Mondays

=IF(ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) = 1, IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) <= 2, WEEKDAY([Session Meeting (End Date)]@row) >= 2), 1, 0), IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) > 2, WEEKDAY([Session Meeting (End Date)]@row) < 2), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 2, IF(OR(WEEKDAY([Session Meeting (Start Date)]@row) > 2, WEEKDAY([Session Meeting (End Date)]@row) < 2), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 1, ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7))))

Total Tuesdays

=IF(ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) = 1, IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) <= 3, WEEKDAY([Session Meeting (End Date)]@row) >= 3), 1, 0), IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) > 3, WEEKDAY([Session Meeting (End Date)]@row) < 3), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 2, IF(OR(WEEKDAY([Session Meeting (Start Date)]@row) > 3, WEEKDAY([Session Meeting (End Date)]@row) < 3), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 1, ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7))))

Total Wednesdays

=IF(ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) = 1, IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) <= 4, WEEKDAY([Session Meeting (End Date)]@row) >= 4), 1, 0), IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) > 4, WEEKDAY([Session Meeting (End Date)]@row) < 4), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 2, IF(OR(WEEKDAY([Session Meeting (Start Date)]@row) > 4, WEEKDAY([Session Meeting (End Date)]@row) < 4), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 1, ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7))))

Total Thursdays

=IF(ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) = 1, IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) <= 5, WEEKDAY([Session Meeting (End Date)]@row) >= 5), 1, 0), IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) > 5, WEEKDAY([Session Meeting (End Date)]@row) < 5), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 2, IF(OR(WEEKDAY([Session Meeting (Start Date)]@row) > 5, WEEKDAY([Session Meeting (End Date)]@row) < 5), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 1, ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7))))

Total Fridays

=IF(ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) = 1, IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) <= 6, WEEKDAY([Session Meeting (End Date)]@row) >= 6), 1, 0), IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) > 6, WEEKDAY([Session Meeting (End Date)]@row) < 6), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 2, IF(OR(WEEKDAY([Session Meeting (Start Date)]@row) > 6, WEEKDAY([Session Meeting (End Date)]@row) < 6), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 1, ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7))))

Total Saturdays

=IF(ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) = 1, IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) <= 7, WEEKDAY([Session Meeting (End Date)]@row) >= 7), 1, 0), IF(AND(WEEKDAY([Session Meeting (Start Date)]@row) > 7, WEEKDAY([Session Meeting (End Date)]@row) < 7), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 2, IF(OR(WEEKDAY([Session Meeting (Start Date)]@row) > 7, WEEKDAY([Session Meeting (End Date)]@row) < 7), ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7) - 1, ROUNDUP((NETDAYS([Session Meeting (Start Date)]@row, [Session Meeting (End Date)]@row)) / 7))))

Total Sessions

=IF(CONTAINS("Sunday", [Session (Days)]@row), [Total Sundays]@row, 0) + IF(CONTAINS("Monday", [Session (Days)]@row), [Total Mondays]@row, 0) + IF(CONTAINS("Tuesday", [Session (Days)]@row), [Total Tuesdays]@row, 0) + IF(CONTAINS("Wednesday", [Session (Days)]@row), [Total Wednesdays]@row, 0) + IF(CONTAINS("Thursday", [Session (Days)]@row), [Total Thursdays]@row, 0) + IF(CONTAINS("Friday", [Session (Days)]@row), [Total Fridays]@row, 0) + IF(CONTAINS("Saturday", [Session (Days)]@row), [Total Saturdays]@row, 0)