Populate Dates and Data With Criteria

[kumara_faith]

Hi,

I have the following table:

Day Expense.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Mon	Food	Lunch	8			Start Date	10/7/2024		10/7/2024	Mon	Food	Lunch	8
2	Mon	Food	Dinner	8			Holiday	10/9/2024		10/7/2024	Mon	Food	Dinner	8
3	Mon	Altar	Flower	15				10/10/2024		10/7/2024	Mon	Altar	Flower	15
4	Mon	Food	Bread	3.5						10/7/2024	Mon	Food	Bread	3.5
5	Tue	Food	Lunch	8						10/8/2024	Tue	Food	Lunch	8
6	Tue	Food	Dinner	8						10/8/2024	Tue	Food	Dinner	8
7	Tue	Automobile	Toll	1.72						10/8/2024	Tue	Automobile	Toll	1.72
8	Tue	Automobile	Toll	10.75						10/8/2024	Tue	Automobile	Toll	10.75
9	Wed	Food	Lunch	8						10/11/2024	Fri	Automobile	Toll	1.72
10	Wed	Food	Dinner	8						10/11/2024	Fri	Automobile	Toll	10.75
11	Wed	Automobile	Toll	1.72						10/11/2024	Fri	Food	Lunch	8
12	Wed	Automobile	Toll	10.75						10/12/2024	Sat	Automobile	Fuel	60
13	Thu	Food	Lunch	8						10/14/2024	Sun	Food	Drink	10
14	Thu	Food	Dinner	8
15	Thu	Automobile	Toll	1.72
16	Thu	Automobile	Toll	10.75
17	Fri	Automobile	Toll	1.72
18	Fri	Automobile	Toll	10.75
19	Fri	Food	Lunch	8
20	Sat	Automobile	Fuel	60
21	Sun	Food	Drink	10
Sheet1

1) Column A to D shows the fixed expenses that will be incurred every week.
2) Cell H1 shows the start date for the week. This may or may not be a Monday
3) Cell H2 to H6 will show the holidays in the week.

I am trying to populate the expenses for the day based on the start date and exclude the holidays. The correct results are in column J to N. Is there a way to use a formula for this ?

 

[myall_blues]

2) Cell H1 shows the start date for the week. This may or may not be a Monday

How should it treat the nature of a "week"? Your criteria above is ambiguous because a week is 7 days regardless of when it starts.
So for example, if your start date in example above was tomorrow (8-Oct-2024) so that the week starts on Tuesday, should the results include Monday anyway because it is then the 7th day of the "week". Or are you implying that the week starts on Monday and the length of the week is reduced depending on the start date (i.e. the week always ends on Sunday so a week commencing on Tuesday only has 6 days)?

 

[kumara_faith]

Hi,

Week here will be Mon to Sunday. If the start day is Tuesday, then the data will be for Tue to Sun. Hope this clarifies. Do let me know if you need further clarification.

 

[myall_blues]

Is this OK? Note my dates are dd/mm/yyyy, and the holiday zone goes to row 8 for reasons required by the formula.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Mon	Food	Lunch	8			Start Date	8/10/2024		Tue	Food	Lunch	8
2	Mon	Food	Dinner	8			Holiday	9/10/2024		Tue	Food	Dinner	8
3	Mon	Altar	Flower	15				10/10/2024		Tue	Automobile	Toll	1.72
4	Mon	Food	Bread	3.5						Tue	Automobile	Toll	10.75
5	Tue	Food	Lunch	8						Thu	Food	Lunch	8
6	Tue	Food	Dinner	8						Thu	Food	Dinner	8
7	Tue	Automobile	Toll	1.72						Thu	Automobile	Toll	1.72
8	Tue	Automobile	Toll	10.75						Thu	Automobile	Toll	10.75
9	Wed	Food	Lunch	8						Fri	Automobile	Toll	1.72
10	Wed	Food	Dinner	8						Fri	Automobile	Toll	10.75
11	Wed	Automobile	Toll	1.72						Fri	Food	Lunch	8
12	Wed	Automobile	Toll	10.75						Sat	Automobile	Fuel	60
13	Thu	Food	Lunch	8						Sun	Food	Drink	10
14	Thu	Food	Dinner	8
15	Thu	Automobile	Toll	1.72
16	Thu	Automobile	Toll	10.75
17	Fri	Automobile	Toll	1.72
18	Fri	Automobile	Toll	10.75
19	Fri	Food	Lunch	8
20	Sat	Automobile	Fuel	60
21	Sun	Food	Drink	10
Sheet1

Cell Formulas
Range	Formula
J1:M13	J1	=LET(_a,CHOOSE(WEEKDAY($H$1,2),7,6,5,4,3,2,1), _b,TEXT(SEQUENCE(_a,1,$H$1),"ddd"), FILTER($A$1:$D$21,ISNUMBER(XMATCH($A$1:$A$21,FILTER(_b,_b<>DROP(TEXT(IF($H$2:$H$8="",$H$2,$H$2:$H$8),"ddd"),7-_a)),0))))
Dynamic array formulas.

 

[kumara_faith]

Hi,

When I use the formula, I am getting as below:

Day Expense.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Mon	Food	Lunch	8			Start Date	8/10/2024		Sat	Automobile	Fuel	60
2	Mon	Food	Dinner	8			Holiday	9/10/2024		Sun	Food	Drink	10
3	Mon	Altar	Flower	15				10/10/2024
4	Mon	Food	Bread	3.5
5	Tue	Food	Lunch	8
6	Tue	Food	Dinner	8
7	Tue	Automobile	Toll	1.72
8	Tue	Automobile	Toll	10.75
9	Wed	Food	Lunch	8
10	Wed	Food	Dinner	8
11	Wed	Automobile	Toll	1.72
12	Wed	Automobile	Toll	10.75
13	Thu	Food	Lunch	8
14	Thu	Food	Dinner	8
15	Thu	Automobile	Toll	1.72
16	Thu	Automobile	Toll	10.75
17	Fri	Automobile	Toll	1.72
18	Fri	Automobile	Toll	10.75
19	Fri	Food	Lunch	8
20	Sat	Automobile	Fuel	60
21	Sun	Food	Drink	10
Sheet2

Cell Formulas
Range	Formula
J1:M2	J1	=LET(_a,CHOOSE(WEEKDAY($H$1,2),7,6,5,4,3,2,1), _b,TEXT(SEQUENCE(_a,1,$H$1),"ddd"), FILTER($A$1:$D$21,ISNUMBER(XMATCH($A$1:$A$21,FILTER(_b,_b<>DROP(TEXT(IF($H$2:$H$8="",$H$2,$H$2:$H$8),"ddd"),7-_a)),0))))
Dynamic array formulas.

 

[myall_blues]

Note my dates are dd/mm/yyyy

Change the dates in column H to suit your region (mm/dd/yyyy)

 

[kumara_faith]

Hi,

Adjusted the date format and have the following.

Day Expense.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Mon	Food	Lunch	8			Start Date	10/7/2024		Mon	Food	Lunch	8
2	Mon	Food	Dinner	8			Holiday	10/9/2024		Mon	Food	Dinner	8
3	Mon	Altar	Flower	15				10/10/2024		Mon	Altar	Flower	15
4	Mon	Food	Bread	3.5						Mon	Food	Bread	3.5
5	Tue	Food	Lunch	8						Tue	Food	Lunch	8
6	Tue	Food	Dinner	8						Tue	Food	Dinner	8
7	Tue	Automobile	Toll	1.72						Tue	Automobile	Toll	1.72
8	Tue	Automobile	Toll	10.75						Tue	Automobile	Toll	10.75
9	Wed	Food	Lunch	8						Thu	Food	Lunch	8
10	Wed	Food	Dinner	8						Thu	Food	Dinner	8
11	Wed	Automobile	Toll	1.72						Thu	Automobile	Toll	1.72
12	Wed	Automobile	Toll	10.75						Thu	Automobile	Toll	10.75
13	Thu	Food	Lunch	8						Fri	Automobile	Toll	1.72
14	Thu	Food	Dinner	8						Fri	Automobile	Toll	10.75
15	Thu	Automobile	Toll	1.72						Fri	Food	Lunch	8
16	Thu	Automobile	Toll	10.75						Sat	Automobile	Fuel	60
17	Fri	Automobile	Toll	1.72						Sun	Food	Drink	10
18	Fri	Automobile	Toll	10.75
19	Fri	Food	Lunch	8
20	Sat	Automobile	Fuel	60
21	Sun	Food	Drink	10
Sheet2

Cell Formulas
Range	Formula
J1:M17	J1	=LET(_a,CHOOSE(WEEKDAY($H$1,2),7,6,5,4,3,2,1), _b,TEXT(SEQUENCE(_a,1,$H$1),"ddd"), FILTER($A$1:$D$21,ISNUMBER(XMATCH($A$1:$A$21,FILTER(_b,_b<>DROP(TEXT(IF($H$2:$H$8="",$H$2,$H$2:$H$8),"ddd"),7-_a)),0))))
Dynamic array formulas.

Note that holidays are 9 and 10 Oct but the table results still show Thursday (10 Oct) . In addition, is there a way to also populate the date in column J as per the priginal post ? Appreciate it.

 

[Alex Blakenburg]

Give this a try:

=FILTER($A$1:$D$21,
        ISNUMBER(
            XMATCH($A$1:$A$21,
                   TEXT(
                        WORKDAY.INTL($H$1-1,
                                     SEQUENCE(NETWORKDAYS.INTL($H$1,$H$1+7-WEEKDAY($H$1,2),"0000000",$H$2:$H$6)),
                                     "0000000",
                                     $H$2:$H$6),
                        "ddd"),
                    0)))

 

[kumara_faith]

Hi Alex,

That worked. Thank you for your solution and appreciate it.

Hi myall,

Thank you for your solution and patience as well.

 

[Alex Blakenburg]

You're welcome. Glad we could help.