Counting Specific Days Between Two Dates

[Meredith_Rimmer]

Hey! I’m trying to count the number of Mondays and Thursdays between two dates - does anyone have a formula or tip on how to achieve this?

[Justin_Barrett]

@ScottWorld @Meredith_Rimmer My post above finally got approved. Not sure why it was held for review, but that’s the perpetual mystery of this forum’s design. :slightly_smiling_face:

@ScottWorld I think you might have misunderstood the structure of those FileMaker formulas. It doesn’t require all 10 formulas to figure out one weekday’s monthly count. There are two formulas for the basic setup, and then one formula per weekday that the user wants to track. In this case, I added one more to automatically calculate the first day of the month, but that can be replaced with a literal date field if desired. That one aside, all that @Meredith_Rimmer needs is four formulas: two for the basic setup, and one each for Monday and Thursday.

[ScottWorld]

Just replied to you, but now it’s pending approval. Lol. Not sure what is going on with the forum today! But yes, I was confused!

[ScottWorld]

Justin_Barrett:

@ScottWorld I think you might have misunderstood the structure of those FileMaker formulas. It doesn’t require all 10 formulas to figure out one weekday’s monthly count. There are two formulas for the basic setup, and then one formula per weekday that the user wants to track. In this case, I added one more to automatically calculate the first day of the month, but that can be replaced with a literal date field if desired. That one aside, all that @Meredith_Rimmer needs is four formulas: two for the basic setup, and one each for Monday and Thursday.

Right, I was confused. This is why I shouldn’t be posting so late at night!!! :crazy_face: :crazy_face: :joy:

[AlliAlosa]

Super creative solution, @Justin_Barrett! :slightly_smiling_face:

A while back, I needed to figure out how many Saturdays were in a given time frame. I posted about it and a user named @Tyler_Kurlas came up with this insane formula:

ROUNDDOWN((1+(DATETIME_DIFF({End Date},{Start Date},'days')-(6-WEEKDAY({Start Date})))/7),0)

The above calculates how many Saturdays fall between two dates. It can be adjusted for different days of the week by changing the “6” to the corresponding day of the week’s value (0-based, starting on Sunday). He included a detailed breakdown of how it works (link below) - but I am honestly still trying to wrap my head around it. Kudos to you, Tyler!

Thought it might be helpful here :slightly_smiling_face:

Include Saturdays in Weekday count? Ask the community

[exploded_sat_calc] Hi Neads, Here’s the steps so you can see it broken down, and a consolidated formula where the temporary variables have been removed. {start day} = WEEKDAY({Start Date}) {days apart} = DATETIME_DIFF({End Date},{Start Date},‘days’) {days until sat} = 6-{start day} {# sats} = ROUNDDOWN(1+({days apart}-{days until sat})/7,0) {consolidated} = ROUNDDOWN((1+(DATETIME_DIFF({End Date},{Start Date},‘days’)-(6-WEEKDAY({Start Date})))/7),0) Good luck!

[Justin_Barrett]

AlliAlosa:

Super creative solution, @Justin_Barrett! :slightly_smiling_face:

I appreciate it, but there’s very little creative about what I did. I just converted the formulas from the post that @ScottWorld shared. As I said earlier, without that link this solution wouldn’t have come together as quickly as it did.

I also appreciate the share of the other formula. However, I wasn’t able to get it to work for any day except Saturday. When changing the 6 to other numbers, the results didn’t match what came out of the other formulas above. For example, I changed the 6 to 0 to try and track Sundays, but the output indicated that some months in my test table allegedly had 6 Sundays in them, which is impossible. There’s probably some more tweaking that could be done to get that single-formula option to work, but that will have to wait for another day.

[kuovonne]

So, I wanted a solution to this issue that would work for any start and end dates (as long as the end date was on or after the start date).

This is what I came up with.

---

My formula checks for the number of whole weeks, and then adds one if the day of the week is included in the partial week left over.

The number of whole weeks is easily determined by using DATETIME_DIFF(End, Start, 'weeks')

The partial week left over is a bit harder to determine. There are two situations: where the leftover days are all in the same week, and where the leftover days are split across two weeks. This formula calculate if the desired weekeday is included in the leftover days by evaluating the WEEKDAY() of the start and end dates for those two situations.

---

In the formula below, replace DesiredWeekday with the number for the day of the week (Sunday = 0, Monday = 1, …)

DATETIME_DIFF(End, Start, 'weeks')
+ 
IF(
  AND(
    WEEKDAY(End) >= WEEKDAY(Start),
    WEEKDAY(End) >= DesiredWeekday,
    WEEKDAY(Start) <= DesiredWeekday
  ),
  1
)
+
IF(
  AND(
    WEEKDAY(End) < WEEKDAY(Start),
    OR(
      WEEKDAY(End) >= DesiredWeekday,
      WEEKDAY(Start) <= DesiredWeekday
    )
  ),
  1
)

---

@Meredith_Rimmer As you can see, there are multiple possible formulas for answering your question. If you find a formula that works for you, could you please mark that one as the solution? This will help anyone reading the thread sift through the different answers.

If you don’t find a formula that works for you, could you please give a bit more details and a screen capture?

[Justin_Barrett]

That’s an awesome solution, @kuovonne! Thanks for sharing it!

[kuovonne]

Thanks, @Justin_Barrett
The formula could be tightened up a bit so there is only one IF, but I’m away from my computer now and can’t write/test a tighter version. I’ll do it when I have access to a computer again.

[Justin_Barrett]

@kuovonne Actually, that can be simplified to use no IF() functions. :winking_face:

Because the AND() function returns True or False, and those are equivalent to 1 and 0 respectively, you can drop the IF() wrappers completely and it still works:

DATETIME_DIFF(End, Start, 'weeks')
+ 
AND(
    WEEKDAY(End) >= WEEKDAY(Start),
    WEEKDAY(End) >= DesiredWeekday,
    WEEKDAY(Start) <= DesiredWeekday
)
+
AND(
    WEEKDAY(End) < WEEKDAY(Start),
    OR(
          WEEKDAY(End) >= DesiredWeekday,
          WEEKDAY(Start) <= DesiredWeekday
    )
)

I think that’s about as compact as it can get. The logic in each of those AND() functions is unique enough that I don’t think they can be combined or simplified any further.

[kuovonne]

That’s part of what I wanted to do, but I was going to combine the first to conditions with an IF so that there would be only one +. I could type it up now, but I want to test it before posting. (I wanted to make sure that true would actually be added as 1, since I hadn’t done it before).

The formula I had took a while to create, and thought I’d post it before after testing but before optimizing because I was running out of time when I’d have access to my computer.

It is also difficult to find the right balance between clarity of how the formula works and conciseness. Since there are no comments in the formula itself, I tend to err on the side of making things explicit.