=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
Week 3 of 5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"] # of Mondays[/TD]
[TD="align: center"]Rank [/TD]
[TD="align: center"]Text[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Mon 17-Sep-18[/TD]
[TD="align: right"]Fri 05-Oct-18[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

3aa​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=CEILING(EOMONTH(A2,0)-5,7)+6[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=RANK(A2,$A$2:$A$6,1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]="Week "&MATCH(A2,{43346;43355;43360;43367;43374},0)&" of "&$C$2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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
Week 1 of 5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"] # of Mondays[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Text[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Mon 03-Sep-18[/TD]
[TD="align: right"]Fri 05-Oct-18[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

</tbody>

3aa​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=CEILING(EOMONTH(A2,0)-5,7)+6[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]="Week "&INT((DAY(A2)+6)/7)&" of "&$C$2







[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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)



[TABLE="width: 1345, align: left"]
<tbody>[TR]
[TD]1st Timesheet In Period
[/TD]
[TD]Actual WC Claiming
[/TD]
[TD]1st Monday
[/TD]
[TD]Last Week To Send Timesheet
[/TD]
[TD]Payable On
[/TD]
[TD]Payable Week Number
[/TD]
[TD]Number Of Timesheets In Period
[/TD]
[TD]Timesheet Number & Number In Period
[/TD]
[/TR]
[TR]
[TD]03/09/2018
[/TD]
[TD]24/09/2018
[/TD]
[TD]03/09/2018
[/TD]
[TD]28/09/2018
[/TD]
[TD]05/10/2018
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]Timesheet 4 Of [/TD]
[/TR]
</tbody>[/TABLE]


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....