Get week numbers and start and end dates based on a Friday to Thursday

B

Brentsa

Board Regular
Joined
Oct 3, 2013
Messages
118
Office Version
  1. 365
Platform
  1. Windows
I have a data that I need to sort out and I'm thinking of adding the following sheet to my workbook to assist so I'm looking for two formulas:

1. A formula to determine the week number based on my set of dates (Week Column). Week will always start on a Friday and end on a Thursday. So formula needs to work even if the first date is a Tuesday and not Friday. ie.
WeekdayDateWeek
Wednesday​
2020/07/01​
1​
Thursday​
2020/07/02​
1​
Friday​
2020/07/03​
2​
Saturday​
2020/07/04​
2​
Sunday​
2020/07/05​
2​
Monday​
2020/07/06​
2​
Tuesday​
2020/07/07​
2​
Wednesday​
2020/07/08​
2​
Thursday​
2020/07/09​
2​
Friday​
2020/07/10​
3​

WeekdayDateWeek
Friday​
2022/07/01​
1​
Saturday​
2022/07/02​
1​
Sunday​
2022/07/03​
1​
Monday​
2022/07/04​
1​
Tuesday​
2022/07/05​
1​
Wednesday​
2022/07/06​
1​
Thursday​
2022/07/07​
1​
Friday​
2022/07/08​
2​
Saturday​
2022/07/09​
2​
Sunday​
2022/07/10​
2​
Monday​
2022/07/11​
2​

2. Formula is based on the results of of the Week column: I want to get the Start Date and End date of Week i.e.:
WeekStart DateEnd Date
1​
2022/07/01​
2022/07/07​
2​
2022/07/08​
2022/07/14​
3​
2022/07/15​
2022/07/21​
4​
2022/07/22​
2022/07/28​
5​
2022/07/29​
2022/08/04​
6​
2022/08/05​
2022/08/11​
7​
2022/08/12​
2022/08/18​
8​
2022/08/19​
2022/08/25​
9​
2022/08/26​
2022/09/01​
10​
2022/09/02​
2022/09/08​

I hope that my request is clear.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
What rule are you following to determine when Week 1 starts?

For example, it looks like Week 1 for the 2021-22 year starts on Friday 26 June 2020. Why then, and not Friday 3 July 2020?
 
Upvote 0
StephenCrump said:
What rule are you following to determine when Week 1 starts?

For example, it looks like Week 1 for the 2021-22 year starts on Friday 26 June 2020. Why then, and not Friday 3 July 2020?
Click to expand...
I'm typing this in manually, so there is no rule. Hoping for someone to assist with a rule and or a formula. The applied rule is that the week starts on a Friday and ends on a Thursday. The data range has to be from 1 July year one to 30 June year two. That is why any day before a Friday will be week 1 regardless the number of days. So if the 1 of July start on Monday, Mon, Tues, Wed, Thurs will be week 1. If however the 1st of July is a Thursdays then week 1 will only consist of one day, Thurs.

I hope I explained myself with regards to your question.
 
Upvote 0
Try this. I've assumed you have the full Excel 365 functionality. Let me know if not, and we can modify the formulae.

I have shown 2027-28, because based on your counting there are 54 weeks in that year.

ABCDEFGH
1Year2027
2
3Week 2 FridayFri 2 Jul 2027
4CONVERT
5WeekStartEndWeek---> StartEnd
61Thu 1 Jul 2027Thu 1 Jul 20274Fri 16 Jul 2027Thu 22 Jul 2027
72Fri 2 Jul 2027Thu 8 Jul 2027
83Fri 9 Jul 2027Thu 15 Jul 2027Date---> Week
94Fri 16 Jul 2027Thu 22 Jul 2027Sat 14 Aug 20278
105Fri 23 Jul 2027Thu 29 Jul 2027
116Fri 30 Jul 2027Thu 5 Aug 2027
127Fri 6 Aug 2027Thu 12 Aug 2027
138Fri 13 Aug 2027Thu 19 Aug 2027
149Fri 20 Aug 2027Thu 26 Aug 2027
1510Fri 27 Aug 2027Thu 2 Sep 2027
1611Fri 3 Sep 2027Thu 9 Sep 2027
1712Fri 10 Sep 2027Thu 16 Sep 2027
1813Fri 17 Sep 2027Thu 23 Sep 2027
1914Fri 24 Sep 2027Thu 30 Sep 2027
2015Fri 1 Oct 2027Thu 7 Oct 2027
2116Fri 8 Oct 2027Thu 14 Oct 2027
2217Fri 15 Oct 2027Thu 21 Oct 2027
2318Fri 22 Oct 2027Thu 28 Oct 2027
2419Fri 29 Oct 2027Thu 4 Nov 2027
2520Fri 5 Nov 2027Thu 11 Nov 2027
2621Fri 12 Nov 2027Thu 18 Nov 2027
2722Fri 19 Nov 2027Thu 25 Nov 2027
2823Fri 26 Nov 2027Thu 2 Dec 2027
2924Fri 3 Dec 2027Thu 9 Dec 2027
3025Fri 10 Dec 2027Thu 16 Dec 2027
3126Fri 17 Dec 2027Thu 23 Dec 2027
3227Fri 24 Dec 2027Thu 30 Dec 2027
3328Fri 31 Dec 2027Thu 6 Jan 2028
3429Fri 7 Jan 2028Thu 13 Jan 2028
3530Fri 14 Jan 2028Thu 20 Jan 2028
3631Fri 21 Jan 2028Thu 27 Jan 2028
3732Fri 28 Jan 2028Thu 3 Feb 2028
3833Fri 4 Feb 2028Thu 10 Feb 2028
3934Fri 11 Feb 2028Thu 17 Feb 2028
4035Fri 18 Feb 2028Thu 24 Feb 2028
4136Fri 25 Feb 2028Thu 2 Mar 2028
4237Fri 3 Mar 2028Thu 9 Mar 2028
4338Fri 10 Mar 2028Thu 16 Mar 2028
4439Fri 17 Mar 2028Thu 23 Mar 2028
4540Fri 24 Mar 2028Thu 30 Mar 2028
4641Fri 31 Mar 2028Thu 6 Apr 2028
4742Fri 7 Apr 2028Thu 13 Apr 2028
4843Fri 14 Apr 2028Thu 20 Apr 2028
4944Fri 21 Apr 2028Thu 27 Apr 2028
5045Fri 28 Apr 2028Thu 4 May 2028
5146Fri 5 May 2028Thu 11 May 2028
5247Fri 12 May 2028Thu 18 May 2028
5348Fri 19 May 2028Thu 25 May 2028
5449Fri 26 May 2028Thu 1 Jun 2028
5550Fri 2 Jun 2028Thu 8 Jun 2028
5651Fri 9 Jun 2028Thu 15 Jun 2028
5752Fri 16 Jun 2028Thu 22 Jun 2028
5853Fri 23 Jun 2028Thu 29 Jun 2028
5954Fri 30 Jun 2028Fri 30 Jun 2028
60
Sheet1
Cell Formulas
RangeFormula
C3C3=LET(d,DATE(C1,7,8),FRI,d-WEEKDAY(d-6),FRI+7*(DAY(FRI)=1))
B6:B59B6=SEQUENCE(53+(C3+363<DATE(C1+1,6,30)))
C6:C59C6=LET(s,C3+7*(B6#-2),BYROW(s,LAMBDA(r,MAX(r,DATE(C1,7,1)))))
D6:D59D6=LET(s,C3+7*(B6#-1)-1,BYROW(s,LAMBDA(r,MIN(r,DATE(C1+1,6,30)))))
G6G6=MAX(C3+7*(F6-2),DATE(C1,7,1))
H6H6=MIN(C3+7*(F6-1)-1,DATE(C1+1,6,30))
G9G9=INT((F9-C3)/7)+2
Dynamic array formulas.
 
Upvote 0
Solution
StephenCrump said:
Try this. I've assumed you have the full Excel 365 functionality. Let me know if not, and we can modify the formulae.

I have shown 2027-28, because based on your counting there are 54 weeks in that year.

ABCDEFGH
1Year2027
2
3Week 2 FridayFri 2 Jul 2027
4CONVERT
5WeekStartEndWeek---> StartEnd
61Thu 1 Jul 2027Thu 1 Jul 20274Fri 16 Jul 2027Thu 22 Jul 2027
72Fri 2 Jul 2027Thu 8 Jul 2027
83Fri 9 Jul 2027Thu 15 Jul 2027Date---> Week
94Fri 16 Jul 2027Thu 22 Jul 2027Sat 14 Aug 20278
105Fri 23 Jul 2027Thu 29 Jul 2027
116Fri 30 Jul 2027Thu 5 Aug 2027
127Fri 6 Aug 2027Thu 12 Aug 2027
138Fri 13 Aug 2027Thu 19 Aug 2027
149Fri 20 Aug 2027Thu 26 Aug 2027
1510Fri 27 Aug 2027Thu 2 Sep 2027
1611Fri 3 Sep 2027Thu 9 Sep 2027
1712Fri 10 Sep 2027Thu 16 Sep 2027
1813Fri 17 Sep 2027Thu 23 Sep 2027
1914Fri 24 Sep 2027Thu 30 Sep 2027
2015Fri 1 Oct 2027Thu 7 Oct 2027
2116Fri 8 Oct 2027Thu 14 Oct 2027
2217Fri 15 Oct 2027Thu 21 Oct 2027
2318Fri 22 Oct 2027Thu 28 Oct 2027
2419Fri 29 Oct 2027Thu 4 Nov 2027
2520Fri 5 Nov 2027Thu 11 Nov 2027
2621Fri 12 Nov 2027Thu 18 Nov 2027
2722Fri 19 Nov 2027Thu 25 Nov 2027
2823Fri 26 Nov 2027Thu 2 Dec 2027
2924Fri 3 Dec 2027Thu 9 Dec 2027
3025Fri 10 Dec 2027Thu 16 Dec 2027
3126Fri 17 Dec 2027Thu 23 Dec 2027
3227Fri 24 Dec 2027Thu 30 Dec 2027
3328Fri 31 Dec 2027Thu 6 Jan 2028
3429Fri 7 Jan 2028Thu 13 Jan 2028
3530Fri 14 Jan 2028Thu 20 Jan 2028
3631Fri 21 Jan 2028Thu 27 Jan 2028
3732Fri 28 Jan 2028Thu 3 Feb 2028
3833Fri 4 Feb 2028Thu 10 Feb 2028
3934Fri 11 Feb 2028Thu 17 Feb 2028
4035Fri 18 Feb 2028Thu 24 Feb 2028
4136Fri 25 Feb 2028Thu 2 Mar 2028
4237Fri 3 Mar 2028Thu 9 Mar 2028
4338Fri 10 Mar 2028Thu 16 Mar 2028
4439Fri 17 Mar 2028Thu 23 Mar 2028
4540Fri 24 Mar 2028Thu 30 Mar 2028
4641Fri 31 Mar 2028Thu 6 Apr 2028
4742Fri 7 Apr 2028Thu 13 Apr 2028
4843Fri 14 Apr 2028Thu 20 Apr 2028
4944Fri 21 Apr 2028Thu 27 Apr 2028
5045Fri 28 Apr 2028Thu 4 May 2028
5146Fri 5 May 2028Thu 11 May 2028
5247Fri 12 May 2028Thu 18 May 2028
5348Fri 19 May 2028Thu 25 May 2028
5449Fri 26 May 2028Thu 1 Jun 2028
5550Fri 2 Jun 2028Thu 8 Jun 2028
5651Fri 9 Jun 2028Thu 15 Jun 2028
5752Fri 16 Jun 2028Thu 22 Jun 2028
5853Fri 23 Jun 2028Thu 29 Jun 2028
5954Fri 30 Jun 2028Fri 30 Jun 2028
60
Sheet1
Cell Formulas
RangeFormula
C3C3=LET(d,DATE(C1,7,8),FRI,d-WEEKDAY(d-6),FRI+7*(DAY(FRI)=1))
B6:B59B6=SEQUENCE(53+(C3+363<DATE(C1+1,6,30)))
C6:C59C6=LET(s,C3+7*(B6#-2),BYROW(s,LAMBDA(r,MAX(r,DATE(C1,7,1)))))
D6:D59D6=LET(s,C3+7*(B6#-1)-1,BYROW(s,LAMBDA(r,MIN(r,DATE(C1+1,6,30)))))
G6G6=MAX(C3+7*(F6-2),DATE(C1,7,1))
H6H6=MIN(C3+7*(F6-1)-1,DATE(C1+1,6,30))
G9G9=INT((F9-C3)/7)+2
Dynamic array formulas.
Click to expand...
Thanks a million it works perfectly. Really appreciate your assistance
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Count of active employees between dates.
Replies
3
Views
631
Fluff
Fluff
M
  • Solved
Formula Help.
Replies
3
Views
429
Fluff
Fluff
N
  • Solved
Formula For Entering Loan Repayment On The Same Day For Each Month
Replies
4
Views
400
Dave Patton
D
D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
368
Eric W
Eric W
T
  • Question Question
Retrieve info with different date formats
2
Replies
11
Views
239
tigerzen
T

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top