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

Get Help
1

I’m entering term dates / a time period (using the Date field with Start + End dates specified).
I’d like it to calculate how many Mondays occur during that time period (inclusive of the start and end dates).
And then I’d repeat the formula for other days so that I’d end up with 5 formulas to cover each weekday.
Separately I’ve used a basic formula to display how many weeks happen between the dates (total days / 7!) and have displayed in different fields the day of the week the period starts on, and ends on. This helps the user manually work out how many of which weekday occur, but I was hoping there was a formula to do the maths for us!
Ideas in simple language would be much appreciated :wink:

IF I can get it to display how many Mondays in a given date range, I’d further like it to calculate something like this:
Where Student is booked onto a session that occurs on a Monday, auto display how many sessions they’re booked on to UNLESS their “Individual Start Date” occurs later than the “Actual Term Start Date” in which case reduce the number of Mondays using their “Individual Start Date” (or do something else like flag this field…?!).

2

You probably need to utilise something like this

If(WeekDayName(Date.Start()) = "Monday",0,
If(WeekDayName(Date.Start()) = "Tuesday",1,
If(WeekDayName(Date.Start()) = "Wednesday",2,
If(WeekDayName(Date.Start()) = "Thursday",3,
If(WeekDayName(Date.Start()) = "Friday",4,
If(WeekDayName(Date.Start()) = "Saturday",5,
6))))))

to take into account which day of the week the time period starts on, and then calculate the number of whole/partial weeks in the time from start to end.

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
4

Thank you, that’s worked! Appreciate that. This means I don’t have to look into paying for a Dates API etc.
(It does mean quite a few extra fields for my intended end user that I don’t want them to touch but I think this is a workable solution.)