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]

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.

[Justin_Barrett]

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?

[ScottWorld]

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]

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

[Justin_Barrett]

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.

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.

[ScottWorld]

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!

[Justin_Barrett]

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

@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:)

[ScottWorld]

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: