Getting a count of consecutive days

[Deano478]

Hi Everyone,

I'm currently having an issue with a counter and i'm not 100% sure if this can actually be done or not but here its goes:

Essentially I have a list of staff members and their planned leave and what I want to do is based on the Leave_Date column I have a counter that will count how many days in a row somebody is off including the weekends. However the biggest caveat with this problem is that for whatever reason the dataset is missing weekends.

So I'm just wondering is there a way to create these missing weekends so that I can actually get an accurate count (This is what i think might not be possible to do).

Here is a dummy dataset similar to the real thing:

In this case this person should be off for 25 consecutive days however because of the missing weekends i'm only getting 16.

Any help at all is appreciated and if if this is simply impossible do let me know. 😀

Name	ID	Leave_Date	Leave_Type	Approval	Year	Email	Consecutive_Leave_Days
Dean Test	12345	12/05/2023	Study Leave	1	2023	Dean.Test@Test.com	1
Dean Test	12345	15/05/2023	Study Leave	1	2023	Dean.Test@Test.com	2
Dean Test	12345	16/05/2023	Study Leave	1	2023	Dean.Test@Test.com	3
Dean Test	12345	17/05/2023	Study Leave	1	2023	Dean.Test@Test.com	4
Dean Test	12345	18/05/2023	Study Leave	1	2023	Dean.Test@Test.com	5
Dean Test	12345	19/05/2023	Study Leave	1	2023	Dean.Test@Test.com	6
Dean Test	12345	22/05/2023	Study Leave	1	2023	Dean.Test@Test.com	7
Dean Test	12345	23/05/2023	Study Leave	1	2023	Dean.Test@Test.com	8
Dean Test	12345	24/05/2023	Study Leave	1	2023	Dean.Test@Test.com	9
Dean Test	12345	25/05/2023	Study Leave	1	2023	Dean.Test@Test.com	10
Dean Test	12345	26/05/2023	Study Leave	1	2023	Dean.Test@Test.com	11
Dean Test	12345	29/05/2023	Study Leave	1	2023	Dean.Test@Test.com	12
Dean Test	12345	30/05/2023	Study Leave	1	2023	Dean.Test@Test.com	13
Dean Test	12345	31/05/2023	Study Leave	1	2023	Dean.Test@Test.com	14
Dean Test	12345	01/06/2023	Study Leave	1	2023	Dean.Test@Test.com	15
Dean Test	12345	02/06/2023	Study Leave	1	2023	Dean.Test@Test.com	16

[FrederikE · Accepted solution]

Hey @Deano478,

I add all dates (in 2023) via data scaffolding and whenever a Saturday/Sunday follows a leave day it is also assigned as a leave day. 

[ChrisTX · Accepted solution]

I came up with 22 consecutive vacation days, because I only counted Saturday and Sunday when Monday was a vacation day.

Chris

[Christina_H · Accepted solution]

Here's a way to do it in a multirow formula.

[Deano478]

Cheers for all the assistance guys I was able to adapt what you have thought me to get a fully scale-able solution in place now