Number of consecutive days a person logs on, ignoring weekends

[craigja]

Im struggling with this one!  I have a dataset of peoples logon dates and the country they logged on in

So:

Name          Logon Country                  Date

Craig            UK                                            2023-04-10

Craig            UK                                            2023-04-11

Craig            UK                                            2023-04-12

Craig            UK                                            2023-04-13

Craig            UK                                            2023-04-17

Craig            UK                                            2023-04-18

Craig            UK                                            2023-04-19

Craig            UK                                            2023-04-20

I want to count the number of consecutive days each person logs on if they are in the same country as the day before (Or the Friday before if today is a Monday), ignoring the weekend.  So in the example above, I would want to get 4 - since Craig logged on Mon-Thurs both weeks, so 2 counts of 4, I cannot get my head round how to do this!

[DataNath · Accepted solution]

Hey @craigja, I can get you a running count of consecutive days but not sure how you want to present the final results:

[craigja]

That looks like it will work, or at least is a great starting point!  Manny thanks