=ceiling(eomonth(a2,0)-5,7)+6

[Trevor3007]

good evening ,


i have send my timehasheets at the end of each week. a2 has the week commencing date (ie 3/9/18) and b2 has the formula =CEILING(EOMONTH(A2,0)-5,7)+6 .

I would like to be able to display what week it was for the period between a2 & b2

IE

1 of 4 / 2 of 4 etc ( or 1 of 5 etc pending on the month)

Hoping some very clued person can resolve this for me & I thank you in advance for you help.

KR
Trevor3007

 

[Dave Patton]

You may be able to adapt the following

Excel 2010
	A	B	C	D	E
1	Start	End	# of Mondays	Rank	Text
2	Mon 01-Oct-18	Fri 02-Nov-18	5	1	Week 1 of 5
3	Mon 08-Oct-18			2	Week 2 of 5
4	Mon 15-Oct-18			3	Week 3 of 5
5	Mon 22-Oct-18			4	Week 4 of 5
6	Mon 29-Oct-18			5	Week 5 of 5
7
8
9	File	T10_1809a	3a
10
3a

Cell Formulas
Range	Formula
B2	=CEILING(EOMONTH(A2,0)-5,7)+6
C2	=NETWORKDAYS.INTL(A2,B2,"0111111")
D2	=RANK(A2,$A$2:$A$6,1)
D3	=RANK(A3,$A$2:$A$6,1)
D4	=RANK(A4,$A$2:$A$6,1)
D5	=RANK(A5,$A$2:$A$6,1)
D6	=RANK(A6,$A$2:$A$6,1)
E2	="Week "&D2&" of "&$C$2
E3	="Week "&D3&" of "&$C$2
E4	="Week "&D4&" of "&$C$2
E5	="Week "&D5&" of "&$C$2
E6	="Week "&D6&" of "&$C$2
A3	=A2+7
A4	=A3+7
A5	=A4+7
A6	=A5+7

 

[Trevor3007]

Hi Dave,

many, many thanks for your help & sorry for the late reply.

I used some of the formulas & seemed to work Ok . however . I done want to use multi rows & I thought I had cracked it but sadly NO.

In your example (E2) i want this to automatically display 1 of 5 ( which worked great for date 3/9/18 & 10/9/18 which returned 2 of 5) When I put the date in A2 17/9/18 it returned 2 of 5 & same for 24/9/18.

Are you able to show me the errors of my ways & sort for me?

Many thank you for your help.
KR
Trevor

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Start	End	# of Mondays	Rank	Text
2	Mon 17-Sep-18	Fri 05-Oct-18	5	1	Week 3 of 5
3
3aa

Cell Formulas
Range	Formula
B2	=CEILING(EOMONTH(A2,0)-5,7)+6
D2	=RANK(A2,$A$2:$A$6,1)
E2	="Week "&MATCH(A2,{43346;43355;43360;43367;43374},0)&" of "&$C$2

You never defined the weeks etc.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Start	End	# of Mondays		Text
2	Mon 03-Sep-18	Fri 05-Oct-18	5		Week 1 of 5
3aa

Cell Formulas
Range	Formula
B2	=CEILING(EOMONTH(A2,0)-5,7)+6
E2	="Week "&INT((DAY(A2)+6)/7)&" of "&$C$2

What result do you want for Oct 1?
How many Mondays are in September?

 

[Trevor3007]

Excel 2010

	A	B	C	D	E
1	Start	End	# of Mondays	Rank	Text
2	Mon 17-Sep-18	Fri 05-Oct-18	5	1	Week 3 of 5
3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

3aa​

Worksheet Formulas Cell Formula B2 =CEILING(EOMONTH(A2,0)-5,7)+6 D2 =RANK(A2,$A$2:$A$6,1) E2 ="Week "&MATCH(A2,{43346;43355;43360;43367;43374},0)&" of "&$C$2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You never defined the weeks etc.

thank you for all you help.

I am so sorry that I may have wasted your time ...however I have learned a lot & shall use your formulas frequently I am sure.

Kindest regards
Trevor3007

 

[Trevor3007]

Excel 2010

	A	B	C	D	E
1	Start	End	# of Mondays		Text
2	Mon 03-Sep-18	Fri 05-Oct-18	5		Week 1 of 5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

3aa​

Worksheet Formulas Cell Formula B2 =CEILING(EOMONTH(A2,0)-5,7)+6 E2 ="Week "&INT((DAY(A2)+6)/7)&" of "&$C$2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

What result do you want for Oct 1?
How many Mondays are in September?

Hi Dave,

Sorry to bother you...but I think I may have cracked it?

A wee tweak here & a wee t'inernet there.....(see below)

1st Timesheet In Period	Actual WC Claiming	1st Monday	Last Week To Send Timesheet	Payable On	Payable Week Number	Number Of Timesheets In Period	Timesheet Number & Number In Period
03/09/2018	24/09/2018	03/09/2018	28/09/2018	05/10/2018	4	4	Timesheet 4 Of

<tbody>
</tbody>

From Left To Right Col A – H & Cells A2 – H2

A2 - Free text (date format dd/mm/yyyy
B2 - Free text (date format dd/mm/yyyy
C2 =DATE(YEAR(EDATE(A2,0)),MONTH(EDATE(A2,0)),CHOOSE(WEEKDAY(DATE(YEAR(EDATE(A2,0)),MONTH(EDATE(A2,0)),1)),2,1,7,6,5,4,3))
D2 =E2-7
E2 =CEILING(EOMONTH($A$2,0)-5,7)+6
F2 =IF(WEEKDAY(B2,2) - DAY(B2) >= 5,
WEEKNUM(DATE(YEAR(B2),MONTH(B2),0),2) - WEEKNUM(DATE(YEAR(B2),MONTH(B2)-1,1),2)
+ (WEEKDAY(DATE(YEAR(B2),MONTH(B2)-1,1),2) < 6),
WEEKNUM(B2,2) - WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)
+ (WEEKDAY(DATE(YEAR(B2),MONTH(B2),1),2) < 6))


G2 =IF(WEEKDAY(D2,2) - DAY(D2) >= 5,
WEEKNUM(DATE(YEAR(D2),MONTH(D2),0),2) - WEEKNUM(DATE(YEAR(D2),MONTH(D2)-1,1),2)
+ (WEEKDAY(DATE(YEAR(D2),MONTH(D2)-1,1),2) < 6),
WEEKNUM(D2,2) - WEEKNUM(DATE(YEAR(D2),MONTH(D2),1),2)
+ (WEEKDAY(DATE(YEAR(D2),MONTH(D2),1),2) < 6))


H2 ="Timesheet"&" "&F2&" "&"Of"&" "&G2

see link of screenshot:-

https://www.amazon.co.uk/clouddrive/share/SrtMD9CLHjd8p0qvB4ubhtBfCqSAmPvgQ4afQm0Q5tI



I have tested and seems to work Ok

All the very best & tallyho....