Help

Re: Counting Specific Days Between Two Dates

3592 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Meredith_Rimmer
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

20 Replies 20

Wow! That’s a very unique problem. I just started messing with it, but there are so many factors that come into play that it’s proving to be pretty challenging. It’s definitely not a one formula solution from what I can see. I’ll keep playing and let you know what I come up with.

Just to give us more to mess with, could you describe your use case in greater detail? What’s the source of the beginning and end dates? What’s the significance of tracking Mondays and Thursdays? How precise does the count need to be?

Yes, as @Justin_Barrett said, what is the specific use case scenario that you’re going for here? Perhaps there is some other way to approach this dilemma.

This would probably take at least a dozen different formulas working together to figure this out — and I don’t even know HOW to figure it out.

Over in the FileMaker forums (which is a completely different database language than Airtable), someone came up with a solution for simply counting the frequency of a given day in a specific month (i.e. whether the day appears 4 or 5 times in a month). And even that seemingly simple task required 10 different fields to come up with the answer!

You can’t use those same formulas in Airtable — and it’s not even solving the same problem — but maybe that can help shed some light on the winding & twisting path that you would need to walk down to attempt solving this dilemma.

Meredith_Rimmer
5 - Automation Enthusiast
5 - Automation Enthusiast

I use Airtable to plan and schedule content for a website. At the beginning of each month, I want to be able to determine the max capacity of stories we can produce as well as the certain “types” of stories.

The number of stories that are produced each month is determined by how many publishing days (M-F) are within that month.

I have it set up where each month has a beginning and end date to help me determine the number of publishing days between the two dates. (I used WORKDAY_DIFF for this) I’m able to take that number and create a formula to figure out the capacity for a given type of content. For example, in June there are 22 days in which we will be publishing content - each day we publish a max of two fashion-related stories and then one beauty-related story. However on Mondays and Thursdays we have an additional shop-related story we publish. Since the number pf publishing days doesn’t really take into account which day of week it is I’m struggling on trying to calculate the number of Mondays and Thursday in a month to help determine the capacity of shop-related stories we can publish.

Let me know if that helps or if I need to provide more details!

@Justin_Barrett @ScottWorld

Thanks for the detailed explanation, @Meredith_Rimmer! That was very helpful, as was the link provided by @ScottWorld. It sounds like what you need, Meredith, is exactly what that FileMaker user shared, and thankfully the FileMaker formula syntax was fairly easy to grasp, so I was able to convert the whole system to Airtable’s structure.

Screen Shot 2020-05-28 at 8.28.01 PM

Here’s a breakdown:

I began with the month names in the primary field, and used a formula in the {Date} field to give me the first date of that month for the current year:

DATETIME_PARSE(Name & " 1, " & YEAR(TODAY()))

The {Days} field calculates the number of days by taking the difference between the current month’s first date and the first date of the next month:

IF({Date}, DATETIME_DIFF(DATEADD({Date}, 1, "months"), {Date}, "days"))

The {Day#} field indicates where the first day of the month falls on a simple 1-7 scale (Sunday = 1, Saturday = 7). This was an easy tweak using Airtable’s built-in WEEKDAY() function:

WEEKDAY({Date}) + 1

The rest of these formulas do the dirty work of figuring out how many iterations of each weekday there are in that month. Whoever wrote that original FileMaker formula series must’ve had fun (or had help) figuring out the pattern, but it’s a pretty basic pattern when you break it down. In your case, all you need are the formulas for the {Monday} and {Thursday} fields, but I’m listing them all here for posterity.

Sunday:

IF(AND({Day#} = 1, Days >= 29), 5, IF(AND({Day#} = 7, Days >= 30), 5, IF(AND({Day#} = 6, Days = 31), 5, 4)))

Monday:

IF(AND({Day#} = 2, Days >= 29), 5, IF(AND({Day#} = 1, Days >= 30), 5, IF(AND({Day#} = 7, Days = 31), 5, 4)))

Tuesday:

IF(AND({Day#} = 3, Days >= 29), 5, IF(AND({Day#} = 2, Days >= 30), 5, IF(AND({Day#} = 1, Days = 31), 5, 4)))

Wednesday:

IF(AND({Day#} = 4, Days >= 29), 5, IF(AND({Day#} = 3, Days >= 30), 5, IF(AND({Day#} = 2, Days = 31), 5, 4)))

Thursday:

IF(AND({Day#} = 5, Days >= 29), 5, IF(AND({Day#} = 4, Days >= 30), 5, IF(AND({Day#} = 3, Days = 31), 5, 4)))

Friday:

IF(AND({Day#} = 6, Days >= 29), 5, IF(AND({Day#} = 5, Days >= 30), 5, IF(AND({Day#} = 4, Days = 31), 5, 4)))

Saturday:

IF(AND({Day#} = 7, Days >= 29), 5, IF(AND({Day#} = 6, Days >= 30), 5, IF(AND({Day#} = 5, Days = 31), 5, 4)))

Thanks again to @ScottWorld for sharing that link! I would probably be banging my head on a much more involved setup if it weren’t for that.

Oh, perfect! So it sounds like you’re always doing this based on calendar months? Your start date and end date is always the start date & end date of a particular calendar month?

That’s perfect, because if you’re simply trying to figure out the number of Mondays & Thursdays in any given calendar month, then you could base your solution upon the exact same logic of those 10 FileMaker formulas that I posted above.

This would take a bit of time to create these 10 formulas, so it’s not something that I can personally volunteer investing the time into doing for you. I’d be happy to do this for you for hire (so please send me a private message if you’d like to chat more about this), or perhaps someone else in the forums would be happy to spend the time creating these 10 formulas for you. :slightly_smiling_face:

But thankfully, someone in that other forum has already figured out the logic to this problem!

@ScottWorld @Meredith_Rimmer I don’t know why my posts suddenly need approval, but my reply from last night was finally approved above. Another reply explaining more is now pending. This is truly driving me nuts.

Meredith_Rimmer
5 - Automation Enthusiast
5 - Automation Enthusiast

@Justin_Barrett thank you so much for the above! That is super helpful and I really appreciate the breakdown. Definitely looks like what I need and am going to try it out now!

@ScottWorld thanks for your help! Going to first try what Justin has provided above but will reach out if I need more assistance:)

Yes, my post from last night needed approval too… just got approved. Very strange. So the ordering of our posts above doesn’t make sense. Lol.

Oh, and you’re totally right — Meredith didn’t need all 10 formulas because she only needed 2 days of the week… but thank you so much, @Justin_Barrett, for doing this for all 7 days of the week! :slightly_smiling_face:

Amazing job! :slightly_smiling_face:

@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.

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!

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

AlliAlosa
10 - Mercury
10 - Mercury

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:

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.

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.
image


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?

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

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.

@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.

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.