Counting a specific day of the week within a time period

Platform discussions
,
1

Hello guys,

I’m facing a task that seems quite challenging to me and I honestly don’t even know where to start.

I need to count the number of occurrences of a specific day of the week in a period of time.

Example:
How many Mondays do we have between the first and last day of April?

As I said before, I don’t know where to start. I’ll post my own ideas in the comments.

2

This might get you started.
https://exceljet.net/formula/count-day-of-week-between-dates

3

Thank you @mharding7va

There’s a hidden function weekday() like the text, mas it doesn’t recognize arrays. I think I can’t make a recurrence to count one by one.

I think it’s impossible to accomplish this task without a third part app.

4

@lindosl

How much would the formula be worth to you?

5

@lindosl

This should do it for you:

ROUNDDOWN((("0" & DAYS({End Date}, {Start Date})) + 0) / 7, 0) + 
  IF(
    OR(
      AND(WEEKDAY({Start Date}) <= WEEKDAY({End Date}), WEEKDAY({Start Date}) <= 2, WEEKDAY({End Date}) >= 2), 
      AND(WEEKDAY({Start Date}) > WEEKDAY({End Date}), OR(WEEKDAY({Start Date}) <= 2, WEEKDAY({End Date}) >= 2))
    )
  , 1, 0)

Just change the four "2"s to the day of the week you want to count. 1 = Monday, 7 = Sunday.

6

Hey a I’ve just found your website! Thank you for the hidden fórmulas.

And thank you again for this formula. I’m studying it. As soon as I understand it I will write it here.

7

@lindosl

The basic concept is the first line counts the number of whole weeks. The IF() potentially adds one more to the count based exactly how the start, end and check day fall in relationship to each other for a partial week.

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: DYNAMIC GROUPING - The Most Powerful monday Feature Ever? - YouTube
Contact me directly here: Contact – The Monday Man

2 Likes
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.