Week count from a specific start date

C

catiexcel

New Member
Joined
Apr 13, 2023
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I have a dataset that is compiled of games from different competitions that have different start date. For example, Super League starts on 10-2-22 so for the first fixture, it should return 0 weeks.
In column A, I want to return the number of weeks between the fixture date (column D) and the competition start date, which differs between year which is found in the Admin tab.
I've tried several lookup formulas but just need a quick answer now as it is holding up a bigger project to feed into Power BI. Any help is much appreciated!!
1691060211421.png


1691060227643.png
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
There is a lot of typing to set that up to test. Could we have the sample data again with XL2BB after you have manually populated at least a few of the rows in column A so that we can see exactly what you are expecting?
 
Upvote 0
I've tried downloading XL2BB a few times but it never works for me, think it's due to the protections on my work laptop. The table that the first image is from has thousands of rows of data and that's just a small bit of it.
1691076590208.png


I've filled in the Start of Comp Year manually for Super League games, and added Weeks since Comp Start into column B, which is what I just want. I am trying to keep this as automated as possible hence why this isn't exactly the answer I'm looking for 😅😅
 
Upvote 0
Thank you! That worked :D I've the mini-sheets below.


Book1
ABCDE
1Start of CompYearCheck DateFixture DateCompetition
2202204/07/202212/02/2022Super League
3202207/07/202219/02/2022Super League
4202208/07/202224/02/2022Super League
5202219/05/202204/03/2022Super League
6202212/05/202211/03/2022Super League
7202209/05/202219/03/2022Super League
8202229/04/202201/04/2022Super League
9202227/04/202214/04/2022Super League
10202217/07/202318/04/2022Super League
11202226/04/202224/04/2022Super League
12202217/07/202329/04/2022Super League
13202218/05/202213/05/2022Super League
14202214/07/202220/05/2022Super League
15202214/07/202204/06/2022Super League
16202215/07/202212/06/2022Super League
17202215/07/202226/06/2022Super League
18202226/07/202202/07/2022Super League
19202226/07/202209/07/2022Super League
20202228/07/202216/07/2022Super League
21202203/08/202223/07/2022Super League
22202203/08/202231/07/2022Super League
23202205/08/2022Super League
24202212/08/2022Super League
25202219/08/2022Super League
26202225/08/2022Super League
27202229/08/2022Super League
28202202/09/2022Super League
29202209/09/2022Super League
30202217/09/2022Super League
Fixture Tracker


Book1
ABCD
1Competitions202220232024
2Super League10/02/202216/02/202314/02/2024
3NRLW23/07/202222/07/202321/07/2024
4Hotplus QLD12/03/202211/03/202310/03/2024
5NRL Premiership03/03/202202/03/202301/03/2024
6Knock On Effect NSW Cup04/03/202203/03/202302/03/2024
Admin
 
Upvote 0
catiexcel said:
Thank you! That worked
Click to expand...
Good news and thanks for the sample data.

Is this what you are trying to do?

catiexcel.xlsm
ABCDE
1Weeks InYearCheck DateFixture DateCompetition
2020224/07/2212/02/22Super League
3120227/07/2219/02/22Super League
4220228/07/2224/02/22Super League
53202219/05/224/03/22Super League
64202212/05/2211/03/22Super League
7520229/05/2219/03/22Super League
87202229/04/221/04/22Super League
99202227/04/2214/04/22Super League
109202217/07/2318/04/22Super League
1110202226/04/2224/04/22Super League
1211202217/07/2329/04/22Super League
1313202218/05/2213/05/22Super League
1414202214/07/2220/05/22Super League
1516202214/07/224/06/22Super League
1617202215/07/2212/06/22Super League
1719202215/07/2226/06/22Super League
1820202226/07/222/07/22Super League
1921202226/07/229/07/22Super League
2022202228/07/2216/07/22Super League
212320223/08/2223/07/22Super League
222420223/08/2231/07/22Super League
232520225/08/22Super League
2426202212/08/22Super League
2527202219/08/22Super League
2628202225/08/22Super League
2728202229/08/22Super League
282920222/09/22Super League
293020229/09/22Super League
3031202217/09/22Super League
Fixture tracker
Cell Formulas
RangeFormula
A2:A30A2=IF(D2="","",INT((D2-XLOOKUP(E2,Admin!A:A,INDEX(Admin!B:D,0,MATCH(B2,Admin!B$1:D$1,0))))/7))
 
Upvote 1
Solution
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question
Week number based on the year starting with a particular date
Replies
2
Views
397
Jeffrey Mahoney
Jeffrey Mahoney
Z
  • Question
Dynamic Working and Week days remaining until end of month from a specific date in the month
Replies
3
Views
160
Peter_SSs
Peter_SSs
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
1K
felixstraube
F
D
  • Question
Add week commencing dates from choosing start of financial year date
Replies
9
Views
426
Fluff
Fluff
Chris_010101
  • Solved
Mail merge with list numbers
Replies
4
Views
232
Chris_010101
Chris_010101

Forum statistics

Threads
1,215,361
Messages
6,124,497
Members
449,166
Latest member
hokjock

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