Count the number of Mondays (or specified weekday) in a date range?

Get Help
3

To provide a concrete answer for how to calculate the number of Mondays in a given date range, I would suggest the following:

  • use the above in a formula field called Weekday that returns a number between 0 and 6
  • use further formulas as follows:

Monday count:

RoundDown(
  (ToDays(Term.End(false) - Term.Start()) +
    If(Weekday - 1 < 0, Weekday + 6, Weekday - 1)) /
    7,
  0
)

Tuesday count

RoundDown(
  (ToDays(Term.End(false) - Term.Start()) +
    If(Weekday - 2 < 0, Weekday + 5, Weekday - 2)) /
    7,
  0
)

and so on.

Note: There are other ways of solving this problem, for example, by creating a db of Dates, and using automation to link each entity to a collection of Dates, based on the start and end dates, and then using formulas like Dates.Filter(WeekDayName(Date) = "Monday).Count()

Ideally, in the future we will improve the handling of dates/time periods to make issues like this easier to solve.

1 Like