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]

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

You don't really state what you are trying to find or solve for? Do you just want a list that shows how many contractual weeks each worker has?

 

[August]

Contractual weeks are not linked to calendar weeks, they are 7days commencing the first day in a contractual week.
For example Martin is working in 4 calendar weeks but only has 3 contractual weeks.
1st week starts 29Oct ends after 7 days on 4Nov
2nd week starts 5Nov ends after 7 days on 11Nov
3rd week starts 15Nov ends 20Nov (final week can be less than 7 days)
I'm trying to write a formula that will give this result

Thanks

 

[Bond00]

Try this =INT((COLUMN(A1)-1)/7)+44
put it under Oct28 and make A1 = whatever cell you put this in, and the +44 is whatever you're starting on. it will + 1 every 7 columns
put that code in a cell and drag it across.

 

[August]

Try this =INT((COLUMN(A1)-1)/7)+44
put it under Oct28 and make A1 = whatever cell you put this in, and the +44 is whatever you're starting on. it will + 1 every 7 columns
put that code in a cell and drag it across.

I'm looking for a formula against each name in their row giving contractual week totals. I couldn't make your solution achieve that.

 

[Bond00]

I'm looking for a formula against each name in their row giving contractual week totals. I couldn't make your solution achieve that.

oh thats funny, i had no idea that's what you were going for. the formula i gave you was to auto make the row below your yellow date row auto make the 44 x7 and 45x7 etc.. go across

try this formula next to the names of the guys. and drag it across so it goes over all the x's in their row. =COUNTIF(B5:Z5,"x")

 

[myall_blues]

@August two things:

• It's always helpful if you can show the expected answer in your example. While in this case I got 3 for Harry I don't know if the others are correct.
• If you update your profile to show the Excel version you are using we can give a more appropriate answer for your version.

That said, does this meet your needs?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1			28-Oct	29-Oct	30-Oct	31-Oct	1-Nov	2-Nov	3-Nov	4-Nov	5-Nov	6-Nov	7-Nov	8-Nov	9-Nov	10-Nov	11-Nov	12-Nov	13-Nov	14-Nov	15-Nov	16-Nov	17-Nov	18-Nov	19-Nov	20-Nov
2		Contractual	44	44	44	44	44	44	44	45	45	45	45	45	45	45	46	46	46	46	46	46	46	47	47	47
3		weeks	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed
4	Harry	3										x	x		x			x	x	x	x			x	x	x
5	John	0
6	Mary	0
7	Arthur	2										x									x
8	Wilson	0
9	Martin	4		x	x	x	x				x		x	x							x			x	x	x
10
Sheet1

Cell Formulas
Range	Formula
C2:Z2	C2	=ISOWEEKNUM(C1)
C3:Z3	C3	=TEXT(C1,"ddd")
B4:B9	B4	=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)

@Bond00 all your COUNTIF formula does is count occurrences of 'x', which in this case for Harry gives a value of 10.

 

[Bond00]

@August two things:

• It's always helpful if you can show the expected answer in your example. While in this case I got 3 for Harry I don't know if the others are correct.
• If you update your profile to show the Excel version you are using we can give a more appropriate answer for your version.

That said, does this meet your needs?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1			28-Oct	29-Oct	30-Oct	31-Oct	1-Nov	2-Nov	3-Nov	4-Nov	5-Nov	6-Nov	7-Nov	8-Nov	9-Nov	10-Nov	11-Nov	12-Nov	13-Nov	14-Nov	15-Nov	16-Nov	17-Nov	18-Nov	19-Nov	20-Nov
2		Contractual	44	44	44	44	44	44	44	45	45	45	45	45	45	45	46	46	46	46	46	46	46	47	47	47
3		weeks	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed
4	Harry	3										x	x		x			x	x	x	x			x	x	x
5	John	0
6	Mary	0
7	Arthur	2										x									x
8	Wilson	0
9	Martin	4		x	x	x	x				x		x	x							x			x	x	x
10
Sheet1

Cell Formulas
Range	Formula
C2:Z2	C2	=ISOWEEKNUM(C1)
C3:Z3	C3	=TEXT(C1,"ddd")
B4:B9	B4	=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)

@Bond00 all your COUNTIF formula does is count occurrences of 'x', which in this case for Harry gives a value of 10.

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

 

[bebo021999]

@myall_blues
If I understand correctly:
Your formula works well untill last row Martin (it should be 3, not 4)
You are supposing that week start from Monday-Sun, then week from 11-17/Nov, though he worked only Fri, but count 1.
Manual calc for Martin:
* Tue 29-Oct to Mon 4-Nov: count 1
* Tue 5-Nov to Mon 11-Nov: count 1
* Fri 15-Nov to End: count 1

 

[August]

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