Date formula by week

[lost_in_the_sauce]

One of our forecasting models needs an overhaul and the current system breaks monthly expenses by category down weekly except it needs an end value for each month, so it throws some weeks off.

Example:
Week 1 Jan1-7
Week 2 Jan 8-14
Week 3 Jan 15-21
Week 4 Jan 22-28
Week 5 Jan 29-31 (report)
Week 6 Feb 1-4
Week 7 Feb 5-12
etc

I'm swapping it to a daily breakout to try and smooth the variances between weeks out but I'm trying to create a formula for which week to pull an expense amount from before dividing it by the days in that week, so that if cell A1 has 1/1/2021, cell B1 has 1/2/2021, cell C1 has 1/3/2021, etc - then the cells directly below in row 2 would have 1/1/2021 though 1/1/2021 and then 1/14/2021 for the next 7, etc (end of week dating for expenses).

 

[JamesCanale]

I might do something using this:

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G
1	Week Number	Start	Finish	Text
2	1	1/1/2021	1/7/2021	Week 1 Jan 1 - 7		1/1/2021	Week 1 Jan 1 - 7
3	2	1/8/2021	1/14/2021	Week 2 Jan 8 - 14		1/2/2021	Week 1 Jan 1 - 7
4	3	1/15/2021	1/21/2021	Week 3 Jan 15 - 21		1/3/2021	Week 1 Jan 1 - 7
5	4	1/22/2021	1/28/2021	Week 4 Jan 22 - 28		1/4/2021	Week 1 Jan 1 - 7
6	5	1/29/2021	1/31/2021	Week 5 Jan 29 - 31		1/5/2021	Week 1 Jan 1 - 7
7	6	2/1/2021	2/4/2021	Week 6 Feb 1 - 4		1/6/2021	Week 1 Jan 1 - 7
8	7	2/5/2021	2/11/2021	Week 7 Feb 5 - 11		1/7/2021	Week 1 Jan 1 - 7
9	8	2/12/2021	2/18/2021	Week 8 Feb 12 - 18		1/8/2021	Week 2 Jan 8 - 14
10	9	2/19/2021	2/25/2021	Week 9 Feb 19 - 25		1/9/2021	Week 2 Jan 8 - 14
11	10	2/26/2021	2/28/2021	Week 10 Feb 26 - 28		1/10/2021	Week 2 Jan 8 - 14
12	11	3/1/2021	3/4/2021	Week 11 Mar 1 - 4		1/11/2021	Week 2 Jan 8 - 14
13	12	3/5/2021	3/11/2021	Week 12 Mar 5 - 11		1/12/2021	Week 2 Jan 8 - 14
14	13	3/12/2021	3/18/2021	Week 13 Mar 12 - 18		1/13/2021	Week 2 Jan 8 - 14
15	14	3/19/2021	3/25/2021	Week 14 Mar 19 - 25		1/14/2021	Week 2 Jan 8 - 14
16	15	3/26/2021	3/31/2021	Week 15 Mar 26 - 31		1/15/2021	Week 3 Jan 15 - 21
17	16	4/1/2021	4/1/2021	Week 16 Apr 1 - 1		1/16/2021	Week 3 Jan 15 - 21
18	17	4/2/2021	4/8/2021	Week 17 Apr 2 - 8		1/17/2021	Week 3 Jan 15 - 21
19	18	4/9/2021	4/15/2021	Week 18 Apr 9 - 15		1/18/2021	Week 3 Jan 15 - 21
20	19	4/16/2021	4/22/2021	Week 19 Apr 16 - 22		1/19/2021	Week 3 Jan 15 - 21
21	20	4/23/2021	4/29/2021	Week 20 Apr 23 - 29		1/20/2021	Week 3 Jan 15 - 21
22	21	4/30/2021	4/30/2021	Week 21 Apr 30 - 30		1/21/2021	Week 3 Jan 15 - 21
23	22	5/1/2021	5/6/2021	Week 22 May 1 - 6		1/22/2021	Week 4 Jan 22 - 28
24	23	5/7/2021	5/13/2021	Week 23 May 7 - 13		1/23/2021	Week 4 Jan 22 - 28
25	24	5/14/2021	5/20/2021	Week 24 May 14 - 20		1/24/2021	Week 4 Jan 22 - 28
26	25	5/21/2021	5/27/2021	Week 25 May 21 - 27		1/25/2021	Week 4 Jan 22 - 28
27	26	5/28/2021	5/31/2021	Week 26 May 28 - 31		1/26/2021	Week 4 Jan 22 - 28
28	27	6/1/2021	6/3/2021	Week 27 Jun 1 - 3		1/27/2021	Week 4 Jan 22 - 28
29	28	6/4/2021	6/10/2021	Week 28 Jun 4 - 10		1/28/2021	Week 4 Jan 22 - 28
30	29	6/11/2021	6/17/2021	Week 29 Jun 11 - 17		1/29/2021	Week 5 Jan 29 - 31
31	30	6/18/2021	6/24/2021	Week 30 Jun 18 - 24		1/30/2021	Week 5 Jan 29 - 31
32	31	6/25/2021	6/30/2021	Week 31 Jun 25 - 30		1/31/2021	Week 5 Jan 29 - 31
33	32	7/1/2021	7/1/2021	Week 32 Jul 1 - 1		2/1/2021	Week 6 Feb 1 - 4
34	33	7/2/2021	7/8/2021	Week 33 Jul 2 - 8		2/2/2021	Week 6 Feb 1 - 4
35	34	7/9/2021	7/15/2021	Week 34 Jul 9 - 15		2/3/2021	Week 6 Feb 1 - 4
36	35	7/16/2021	7/22/2021	Week 35 Jul 16 - 22		2/4/2021	Week 6 Feb 1 - 4
37	36	7/23/2021	7/29/2021	Week 36 Jul 23 - 29		2/5/2021	Week 7 Feb 5 - 11
38	37	7/30/2021	7/31/2021	Week 37 Jul 30 - 31		2/6/2021	Week 7 Feb 5 - 11
39	38	8/1/2021	8/5/2021	Week 38 Aug 1 - 5		2/7/2021	Week 7 Feb 5 - 11
40	39	8/6/2021	8/12/2021	Week 39 Aug 6 - 12		2/8/2021	Week 7 Feb 5 - 11
41	40	8/13/2021	8/19/2021	Week 40 Aug 13 - 19
42	41	8/20/2021	8/26/2021	Week 41 Aug 20 - 26
43	42	8/27/2021	8/31/2021	Week 42 Aug 27 - 31
44	43	9/1/2021	9/2/2021	Week 43 Sep 1 - 2
45	44	9/3/2021	9/9/2021	Week 44 Sep 3 - 9
46	45	9/10/2021	9/16/2021	Week 45 Sep 10 - 16
47	46	9/17/2021	9/23/2021	Week 46 Sep 17 - 23
48	47	9/24/2021	9/30/2021	Week 47 Sep 24 - 30
49	48	10/1/2021	10/7/2021	Week 48 Oct 1 - 7
50	49	10/8/2021	10/14/2021	Week 49 Oct 8 - 14
51	50	10/15/2021	10/21/2021	Week 50 Oct 15 - 21
52	51	10/22/2021	10/28/2021	Week 51 Oct 22 - 28
53	52	10/29/2021	10/31/2021	Week 52 Oct 29 - 31
54	53	11/1/2021	11/4/2021	Week 53 Nov 1 - 4
55	54	11/5/2021	11/11/2021	Week 54 Nov 5 - 11
56	55	11/12/2021	11/18/2021	Week 55 Nov 12 - 18
57	56	11/19/2021	11/25/2021	Week 56 Nov 19 - 25
58	57	11/26/2021	11/30/2021	Week 57 Nov 26 - 30
59	58	12/1/2021	12/2/2021	Week 58 Dec 1 - 2
60	59	12/3/2021	12/9/2021	Week 59 Dec 3 - 9
61	60	12/10/2021	12/16/2021	Week 60 Dec 10 - 16
62	61	12/17/2021	12/23/2021	Week 61 Dec 17 - 23
63	62	12/24/2021	12/30/2021	Week 62 Dec 24 - 30
64	63	12/31/2021	12/31/2021	Week 63 Dec 31 - 31
Sheet8

Cell Formulas
Range	Formula
C2	C2	=IF(B2+6>EOMONTH(B2,0),EOMONTH(B2,0),B2+6)
D2:D64	D2	="Week " & A2&" "& TEXT(B2,"mmm ")&DAY(B2)& " - "&DAY(C2)
A3:A64	A3	=A2+1
B3:B64	B3	=C2+1
C3:C64	C3	=IF(B3+6>EOMONTH(B3,0),EOMONTH(B3,0),B3+IF(MOD(C2-$B$2,7)=6,6,5-MOD(C2-$B$2,7)))
G2:G40	G2	=INDEX($D$2:$D$64,MATCH(F2,$B$2:$B$64,1))

 

[lost_in_the_sauce]

Beautiful. I love you.

 

[lost_in_the_sauce]

Have a follow up if you don't mind - just because of the formatting the data gets sent to me in, I swapped the rows and columns, but I'm getting an issue where if the day is the last day, I get a week with 1 day - if you continue your formula in column G down, you might run into it for April.

 

[JamesCanale]

There are definitely some 1-day 'weeks'. What would you prefer it to be?