Populate Date With Criteria

[kumara_faith]

Hi,

I have the following table:

Lifetime Planner_2024 v.4.xlsx
	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW
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/13/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
Daily Expense Tracker

Cell Formulas
Range	Formula
DT1:DW13	DT1	=FILTER($DJ$1:$DM$21, ISNUMBER( XMATCH($DJ$1:$DJ$21, TEXT( WORKDAY.INTL($DQ$1-1, SEQUENCE(NETWORKDAYS.INTL($DQ$1,$DQ$1+7-WEEKDAY($DQ$1,2),"0000000",$DQ$2:$DQ$6)), "0000000", $DQ$2:$DQ$6), "ddd"), 0)))
Dynamic array formulas.

Column DJ to DM shows the fixed expenses that will be incurred every week (Mon-Sun). Cell DQ1 shows the start day of the week. DQ2 to DQ6 shows the public holidays. Column DT to DW shows the expenses populated based on the start date and excludes the public holidays.

Is there a way to populate the dates as well in column DS ? Appreciate all the help.

 

[kumara_faith]

Hi,

Appreciate any help.

 

[Peter_SSs]

Like this? I have just added a bit to the existing formula (though I did also remove all the "$" signs from that original formula as they are not required and just clutter the formula).

24 10 09.xlsm
	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW
1	Mon	Food	Lunch	8			Start Date	7/10/2024		7/10/2024	Mon	Food	Lunch	8
2	Mon	Food	Dinner	8			Holiday	9/10/2024		7/10/2024	Mon	Food	Dinner	8
3	Mon	Altar	Flower	15				10/10/2024		7/10/2024	Mon	Altar	Flower	15
4	Mon	Food	Bread	3.5						7/10/2024	Mon	Food	Bread	3.5
5	Tue	Food	Lunch	8						8/10/2024	Tue	Food	Lunch	8
6	Tue	Food	Dinner	8						8/10/2024	Tue	Food	Dinner	8
7	Tue	Automobile	Toll	1.72						8/10/2024	Tue	Automobile	Toll	1.72
8	Tue	Automobile	Toll	10.75						8/10/2024	Tue	Automobile	Toll	10.75
9	Wed	Food	Lunch	8						11/10/2024	Fri	Automobile	Toll	1.72
10	Wed	Food	Dinner	8						11/10/2024	Fri	Automobile	Toll	10.75
11	Wed	Automobile	Toll	1.72						11/10/2024	Fri	Food	Lunch	8
12	Wed	Automobile	Toll	10.75						12/10/2024	Sat	Automobile	Fuel	60
13	Thu	Food	Lunch	8						13/10/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
Include Date

Cell Formulas
Range	Formula
DS1:DW13	DS1	=LET(f,FILTER(DJ1:DM21, ISNUMBER( XMATCH(DJ1:DJ21, TEXT( WORKDAY.INTL(DQ1-1, SEQUENCE(NETWORKDAYS.INTL(DQ1,DQ1+7-WEEKDAY(DQ1,2),"0000000",DQ2:DQ6)), "0000000", DQ2:DQ6), "ddd"), 0))), d,BYROW(TAKE(f,,1),LAMBDA(r,DQ1+(FIND(r,"MonTueWedThuFriSatSun")-1)/3)), HSTACK(d,f))
Dynamic array formulas.

 

[kumara_faith]

Hi Peter,

Thank you for the solution. That worked. Appreciate it and have a great day.

 

[Peter_SSs]

Cheers.

 

[Tetra201]

I don't know if this matters, but formula from Post #3 seems to stumble if the start date is a non-Monday. If this does matter, here is an alternative approach:

=LET(c,INDEX(DJ1:DJ21,MATCH(TEXT(DQ1,"ddd"),DJ1:DJ21,0)),d,DQ1-WEEKDAY(DQ1,11)+(SEARCH(c:DJ21,"MonTueWedThuFriSatSun")+2)/3,FILTER(HSTACK(d,c:DM21),ISNA(XMATCH(d,DQ2:DQ6,0))))

 

[Peter_SSs]

I don't know if this matters, but formula from Post #3 seems to stumble if the start date is a non-Monday.

You are right, but I believe that we were given that information in post 1

Column DJ to DM shows the fixed expenses that will be incurred every week (Mon-Sun). Cell DQ1 shows the start day of the week