Contractual Weeks Calculation

[August]

In the attached a contractual week starts at the first work day marked x and ends 7days later. The next contractual week starts on the next work day marked x and end 7 days later. The final contractual week can have less than 7 days. The No of contractual weeks for Harry would therefore be 3.
I have tried many variants of sumproducts/countifs/mod but cannot get a formula that works.
Does anyone have an idea how I could achieve the corrrect result in a formula

Many thanks

 

[Bond00]

The issue is theres no real pattern to automate it with.
He was basing his formula off of the contractual weeks 44-47 on row 2, and as you can see Martin has an x under each week # 44, 45, 46, and 47. But now we learn that isn't what its based on.
You could try just always counting 4 x's in a row no matter if it has a space or 2 in-between (weekends etc) but that also isn't always constant because sometimes there is only 1 to 3 x's in a contractual week, like with Arthur or Martin.
So without some sort of constant or pattern this will never be fully automated.

What you could do is use =COUNTIF(C4:Z4,"x") (in col C) and also (in col B) =IF(COUNTIFS(C4:Z4,"x")=0,0,INT((MAX(IF($C4:$Z4="x",COLUMN($C4:$Z4)))-MIN(IF($C4:$Z4="x",COLUMN($C4:$Z4))))/7)+1)
That way you get actual # of days they worked and approx. # of weeks they worked based on the week # and get at least a good feel for it then, but you would always need to confirm since there is no perfect rule set for it.

 

[myall_blues]

Thank you all for taking the time to reply
You are correct Martin should be 3 and contractual weeks start on the first day of work (x) not Monday.
I'll try the solution from myall_blues over the weekend and see how I get on

Again many many thanks for your time

Which version of Excel are you using?

 

[Tetra201]

Here is a user-defined function (UDF) for you to try.
Usage example: =CWeeks(B5:Y5)

Function CWeeks(rng As Range) As Long
    Application.Volatile
    Dim s As String, cll As Range
    s = "": CWeeks = 0
    For Each cll In rng.Resize(1)
        s = s & IIf(cll = "x", "x", " ")
    Next cll
    s = LTrim(s)
    Do While Len(s) > 0
        CWeeks = CWeeks + 1
        s = LTrim(Mid(s, 8))
    Loop
End Function

 

[Bond00]

Here is a user-defined function (UDF) for you to try.
Usage example: =CWeeks(B5:Y5)

Function CWeeks(rng As Range) As Long
    Application.Volatile
    Dim s As String, cll As Range
    s = "": CWeeks = 0
    For Each cll In rng.Resize(1)
        s = s & IIf(cll = "x", "x", " ")
    Next cll
    s = LTrim(s)
    Do While Len(s) > 0
        CWeeks = CWeeks + 1
        s = LTrim(Mid(s, 8))
    Loop
End Function

does that work in 2016? Wasn't working for me when i tested it.

 

[August]

true, forgot he just wanted week counts and not just a count of days worked per person.

I've updated my profile and attached is the sheet with desired results under Weeks Worked

 

[myall_blues]

I've updated my profile and attached is the sheet with desired results under Weeks Worked

Have you tried the code provided by @Tetra201? It works well and I'm not sure a formula solution is possible for your problem.

Book1
	A	B	C
1			28/10/2024
2		Contractual	44
3		weeks	Mon
4	Harry	3
5	John	0
6	Mary	0
7	Arthur	2
8	Wilson	0
9	Martin	3
10	George	0
11	Ivy	0
12	Helen	1
13	Edward	2	x
14	Mabel	1	x
15	Monty	3
16	August	3
Sheet1

Cell Formulas
Range	Formula
C2	C2	=ISOWEEKNUM(C1)
C3	C3	=TEXT(C1,"ddd")
B4:B16	B4	=cweeks(C4:Z4)

 

[August]

The code works perfectly thank you Bond00. !!!

 

[August]

One question.
On the actual document the "x" will be either "SW", "W" or "SWF" (representing Start Work, Work or Start Work Finish)
Can the x be substituted in the code with the above.
I've tried myself without any success

Thanks

 

[August]

Correction could also be "WF (Work Finish)
Entered the below from Copilot but this gives all zeros.

Function CWeeks(rng As Range) As Long
Application.Volatile
Dim s As String, cll As Range
s = "": CWeeks = 0
For Each cll In rng
If cll.Value = "SW" Or cll.Value = "W" Or cll.Value = "SWF" Or cll.Value = "WF" Then
s = s & "x"
Else
s = s & " "
End If
Next cll
s = Trim(s)
If Len(Replace(s, " ", "")) = 0 Then
CWeeks = 0
Else
Do While Len(s) > 0
If Left(s, 1) = "x" Then
CWeeks = CWeeks + 1
s = Mid(s, 8)
Else
s = Mid(s, 2)
End If
Loop
End If
End Function

The code works perfectly thank you Bond00. !!!

Apologies Tetra201 it was your code that did the trick

 

[August]

Here is a user-defined function (UDF) for you to try.
Usage example: =CWeeks(B5:Y5)

Function CWeeks(rng As Range) As Long
    Application.Volatile
    Dim s As String, cll As Range
    s = "": CWeeks = 0
    For Each cll In rng.Resize(1)
        s = s & IIf(cll = "x", "x", " ")
    Next cll
    s = LTrim(s)
    Do While Len(s) > 0
        CWeeks = CWeeks + 1
        s = LTrim(Mid(s, 8))
    Loop
End Function

Thank you works perfectly with "x"
Is there a way to use "SW", "W", "WF" and "SWF" in the code as these are the markers to denote days worked.

Thanks again