Travel Planner sort by start date without month in travel ID

Spreadsheets
1

Hello.

I am looking at this function in cell A11 of the Travel Planner sheet.

={“Tag”;if(D5=“Trip Id”,iferror(sort(filter(T4:T,N4:N=D6))),iferror(sort(filter(T4:T,date(D6,1,1)<=vlookup(N4:N,{L4:L,H4:H},2,true),date(D6+1,1,1)>=vlookup(N4:N,{L4:L,H4:H},2,true)))))}

I would like to get rid of the month in the travel ID but still have the budget sort by the proposed start date in column H. Changing the formula to remove the month from the Trip ID is straighforward (={“Trip Id”;arrayformula(if(isblank(H4:H),iferror(1/0),text(H4:H,“yyyy**-mm**”)&“-”&substitute(J4:J," “,”")))}

Any idea if this is feasible?

Thanks!

2

When I try this I get an error message in A11

={“Tag”;if(D5=“Trip Id”,iferror(sort(filter(H4:T,N4:N=D6)1,true)),iferror(sort(filter(H4:T,date(D6,1,1)<=vlookup(N4:N,{L4:L,H4:H},2,true),date(D6+1,1,1)>=vlookup(N4:N,{L4:L,H4:H},2,true)))1,true))}

3

I love your formula @PCB ,

Try this:

={“Tag”;if(D5=“Trip Id”,iferror(sort(filter(T4:T,N4:N=D6))),iferror(sort(filter(T4:T, YEAR(H4:H)=D6))))}

-Alice
Tiller Evangelist

Bluesky, Instagram, Facebook, LinkedIn