Given two dates, calculate the number of weekend days between them (saturdays/sundays)

[BS_THE_ANALYST]

Hi all!

I was just working on Weekly Challenge 41 when I was inspired by this particular problem. 

Can we write a formula where we input two dates, and it will output the number of weekend days in between them? (inclusive of both dates)

For example: given these two dates -  Wednesday 28th December 2022 & Saturday 14th January 2023. We can see, by manually counting, that there are 5 weekend days between them.

I'd love to see some different takes on tackling this question! 

Thanks, 

BS

[Felipe_Ribeir0 · Accepted solution]

Hi @BS_THE_ANALYST 

You can use the generate rows tool to generate all dates between the 2 dates, filter the weekends and count them.

[DataNath · Accepted solution]

Hey @BS_THE_ANALYST, the regular way to currently do this (which is a popular question/request, just normally the other way around i.e. excluding weekends!) is to generate the dates between the Start/End and then filter out those we aren't interested in before performing a count. Here's a quick example:

2 records containing Start/End dates:

After assigning a RecordID (which we need to join the count back to the right record at the end), we generate the rows in between by basically telling Alteryx to begin at our start date and add a day until we reach our end date):

Following our row generation, we filter out days that aren't what we want (weekdays in this scenario). DateTimeFormat(<field>, '%a') temporarily converts our date field to the shortened version of it's weekday i.e. Mon through Sun, for the duration of the Filter evaluation and we're therefore filtering out all values that don't correspond to Saturday/Sunday:

Next, we group by our RecordID (to make sure we're only counting for each individual Start/End date that we began with), and do a count of the days that made it through the Filter i.e. weekends:

Finally, we finish up by using a simple Join to place these counts next to the records they belong to. The join tool has built-in Select functionality and so we just use this to remove our RecordIDs and tidy things up:

Hope this helps! Please feel free to shout if you have any further questions.

[BS_THE_ANALYST]

@Felipe_Ribeir0 I do wonder though, is it possible to do this by just using formulas and not generating rows to count the occurrences of the weekend days between them? Nevertheless, beautiful solution!

The generating rows part feels somehow forced and not as efficient if the number of days between the dates is big. Let's imagine there are millions of days between the two dates (theoretically, of course 😂). Generating rows could be more taxing than a formula combined with a few IF ELSEs? 

Again, great solution.

[BS_THE_ANALYST]

Thanks for this @DataNath ! 

Loved your approach to the solution! I just had a feeling there must be a way to do this with a few cheeky formulas. I've seen many people using the mod function with date times and blitzing solutions in just a couple of steps! 

If I manage to find an abstract way with a couple of formulas, I'll let you know! 

[Felipe_Ribeir0]

Hi @BS_THE_ANALYST 

The usual way to do this is by generating rows. But there is this post made by jdunkerley79 that propose a different way to count working week days using just formulas. I saw this this week and did not had enough time to go deeper on it, but maybe you can adapt it to count weekends.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Datetimediff-using-only-weekdays-busin...

[Felipe_Ribeir0 · Accepted solution]

Hi @BS_THE_ANALYST 

I adapted what he did and tested it with some dates and it seems be working fine comparing with the regular way (generating rows):

[BS_THE_ANALYST]

@Felipe_Ribeir0 

That's actually ace! Thanks so much for this!

[Shifty]

@BS_THE_ANALYST, be careful saying "ace" to people on here - they get excited!  🤣🤣 

Ace solution @Felipe_Ribeir0!  😁

[Felipe_Ribeir0]

@Shifty