Making a tough Excel Formula take one input as a range, and output as a Spill Array/Range.

T

TravisJBennett

New Member
Joined
Dec 28, 2012
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I've made a (really neat, and tough to write!) formula that evaluates a single date against a single cell's value, where that cell contains multiple date ranges, and then indicate whether or not the date is within any of the listed multiple date ranges.

The multiple date ranges are formatted in a familiar manner: Like when printing multiple select pages from whatever app, e.g. 1-2, 5-8, 15-34
For this example, my date format is M/D/YYYY. We'll disregard other/international date representations for the moment (honestly, I use them all).

Example Date Ranges (crammed as text into a single cell): 9/1/2022-9/8/2022,9/10/2022-9/10/2022,9/12/2022-9/25/2022,10/1/2022-10/1/2022
Input: 9/5/2022
Output: TRUE

That all works great! I love it, and despite the complexity of jamming all these dates into one cell, is exactly what I need (for phase 1).

HELP!
So how can I adapt a copy of my formula in $C$12, so that for Example Date Ranges, it can accept a range instead of a single cell, and operate one cell at a time to determine TRUE vs. FALSE. then output a SPILL range of TRUEs and FALSEs ?

DateRangeDemo.xlsx
ABCDEFGHIJ
1
2Given: A comma-separated list of multiple dates and date-ranges (like one might enter when printing a custom page range...)
3Three Examples (we'll start on the right with just the top cell…)Processing a single cell's dates and date ranges…
49/1/2022-9/8/2022,9/10/2022-9/10/2022,9/12/2022-9/25/2022,10/1/2022-10/1/2022 } ----------------->9/1/2022-9/8/2022aka4480544812
59/30/2022-10/4/2022,10/8/2022-10/12/2022,10/20/2022-10/31/2022<-- used below9/10/2022-9/10/20224481444814
610/25/2022-11/02/2022,11/7/2022-11/15/2022,11/18/2022<-- used below9/12/2022-9/25/20224481644829
710/1/2022-10/1/20224483544835
8
9Checking a value against a single cell containing CSV multiple ranges and/or values, in this case dates and having strict midnight values (integer dates).
10Checking this date:44814(aka, 9/10/2022)
11
12In Range?TRUE
13Out of Range?FALSE
14
15Everything here and above works great!
16My question: How do I adapt my "In Range?" formula, to check the one highlighted date against each cell with $A$4:$A$6, and get results back in a nice spill array (e.g. of 3Rx1C for this short example)?
Sheet1
Cell Formulas
RangeFormula
C4:C7C4=CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE($A$4, ",", "</s><s>") & "</s></t>", "//s"))
E4:F7E4=CHOOSE({1,2}, CONCATENATE(FILTERXML("<t><b>" & SUBSTITUTE(SUBSTITUTE($A$4, ",", "</e><b>"),"-","</b><e>") & "</e></t>", "//b")), CONCATENATE(FILTERXML("<t><b>" & SUBSTITUTE(SUBSTITUTE($A$4, ",", "</e><b>"),"-","</b><e>") & "</e></t>", "//e")) )
E10E10="(aka, "&TEXT(C10,"M/D/YYYY")&")"
C12C12=LET( UsingDate,$C$10, DateSpans,$A$4, SingleDatesExpanded,TEXTJOIN(",",FALSE,SUBSTITUTE( IF(LEN(CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")))<=11, CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s"))&"-"&CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")), (CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")))), "$","")), DateArray, CHOOSE({1,2}, CONCATENATE(FILTERXML("<t><b>" & SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded, ",", "</e><b>"),"-","</b><e>") & "</e></t>", "//b")), CONCATENATE(FILTERXML("<t><b>" & SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded, ",", "</e><b>"),"-","</b><e>") & "</e></t>", "//e"))), SUMPRODUCT(--(UsingDate>=VALUE(INDEX(DateArray,,1))),--(UsingDate<=VALUE(INDEX(DateArray,,2)))) )=1
C13C13=NOT(C12)
Dynamic array formulas.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Why can I not edit a post I just made?

Here's a much cleaner sheet...
DateRangeDemo.xlsx
ABCDEF
1
2Given: A comma-separated list of multiple dates and date-ranges…(like one might enter when printing a custom page range...)
3Three Examples (we'll start on the right with just the top cell…)Processing a single cell's dates and date ranges…
49/1/2022-9/8/2022,9/10/2022,9/12/2022-9/25/2022,10/1/2022 } ----------------->9/1/2022-9/8/2022aka4480544812
59/30/2022-10/4/2022,10/8/2022-10/12/2022,10/20/2022-10/31/2022<-- used below448144481444814
610/25/2022-11/02/2022,11/7/2022-11/15/2022,11/18/2022<-- used below9/12/2022-9/25/20224481644829
7448354483544835
8
9Checking a value against a single cell containing CSV multiple ranges and/or values…
10in this case dates and having strict midnight values (integer dates).Checking this date:44814(aka, 9/10/2022)
11
12In Range?TRUE
13Out of Range?FALSE
14
15Everything here and above works great!
16My question: How do I adapt my "In Range?" formula, to check the one highlighted date
17against each cell with $A$4:$A$6…
18...and get results back in a nice spill array (e.g. of 3Rx1C for this short example)?
Sheet1
Cell Formulas
RangeFormula
C4:C7C4=CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE($A$4, ",", "</s><s>") & "</s></t>", "//s"))
E4:F7E4=LET( UsingDate,$C$10, DateSpans,$A$4, SingleDatesExpanded,TEXTJOIN(",",FALSE,SUBSTITUTE( IF(LEN(CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")))<=11, CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s"))&"-"&CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")), (CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")))), "$","")), DateArray, CHOOSE({1,2}, CONCATENATE(FILTERXML("<t><b>" & SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded, ",", "</e><b>"),"-","</b><e>") & "</e></t>", "//b")), CONCATENATE(FILTERXML("<t><b>" & SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded, ",", "</e><b>"),"-","</b><e>") & "</e></t>", "//e"))), DateArray )
E10E10="(aka, "&TEXT(C10,"M/D/YYYY")&")"
C12C12=LET( UsingDate,$C$10, DateSpans,$A$4, SingleDatesExpanded,TEXTJOIN(",",FALSE,SUBSTITUTE( IF(LEN(CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")))<=11, CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s"))&"-"&CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")), (CONCATENATE(FILTERXML("<t><s>" & SUBSTITUTE(DateSpans, ",", "$</s><s>") & "$</s></t>", "//s")))), "$","")), DateArray, CHOOSE({1,2}, CONCATENATE(FILTERXML("<t><b>" & SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded, ",", "</e><b>"),"-","</b><e>") & "</e></t>", "//b")), CONCATENATE(FILTERXML("<t><b>" & SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded, ",", "</e><b>"),"-","</b><e>") & "</e></t>", "//e"))), SUMPRODUCT(--(UsingDate>=VALUE(INDEX(DateArray,,1))),--(UsingDate<=VALUE(INDEX(DateArray,,2)))) )=1
C13C13=NOT(C12)
Dynamic array formulas.
 
Upvote 0
How about
Excel Formula: 
=BYROW(A4:A6,LAMBDA(DateSpans,LET(
UsingDate,$C$10,
SingleDatesExpanded,TEXTJOIN(",",FALSE,SUBSTITUTE(IF(LEN(CONCATENATE(FILTERXML("<t><s>"&SUBSTITUTE(DateSpans,",","$</s><s>")&"$</s></t>","//s")))<=11,CONCATENATE(FILTERXML("<t><s>"&SUBSTITUTE(DateSpans,",","$</s><s>")&"$</s></t>","//s"))&"-"&CONCATENATE(FILTERXML("<t><s>"&SUBSTITUTE(DateSpans,",","$</s><s>")&"$</s></t>","//s")),(CONCATENATE(FILTERXML("<t><s>"&SUBSTITUTE(DateSpans,",","$</s><s>")&"$</s></t>","//s")))),"$","")),
DateArray,CHOOSE({1,2},CONCATENATE(FILTERXML("<t><b>"&SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded,",","</e><b>"),"-","</b><e>")&"</e></t>","//b")),CONCATENATE(FILTERXML("<t><b>"&SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded,",","</e><b>"),"-","</b><e>")&"</e></t>","//e"))),
SUMPRODUCT(--(UsingDate>=VALUE(INDEX(DateArray,,1))),--(UsingDate<=VALUE(INDEX(DateArray,,2))))
)=1))
 
Upvote 0
Solution
Fluff said:
How about
Excel Formula: 
=BYROW(A4:A6,LAMBDA(DateSpans,LET(
UsingDate,$C$10,
SingleDatesExpanded,TEXTJOIN(",",FALSE,SUBSTITUTE(IF(LEN(CONCATENATE(FILTERXML("<t><s>"&SUBSTITUTE(DateSpans,",","$</s><s>")&"$</s></t>","//s")))<=11,CONCATENATE(FILTERXML("<t><s>"&SUBSTITUTE(DateSpans,",","$</s><s>")&"$</s></t>","//s"))&"-"&CONCATENATE(FILTERXML("<t><s>"&SUBSTITUTE(DateSpans,",","$</s><s>")&"$</s></t>","//s")),(CONCATENATE(FILTERXML("<t><s>"&SUBSTITUTE(DateSpans,",","$</s><s>")&"$</s></t>","//s")))),"$","")),
DateArray,CHOOSE({1,2},CONCATENATE(FILTERXML("<t><b>"&SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded,",","</e><b>"),"-","</b><e>")&"</e></t>","//b")),CONCATENATE(FILTERXML("<t><b>"&SUBSTITUTE(SUBSTITUTE(SingleDatesExpanded,",","</e><b>"),"-","</b><e>")&"</e></t>","//e"))),
SUMPRODUCT(--(UsingDate>=VALUE(INDEX(DateArray,,1))),--(UsingDate<=VALUE(INDEX(DateArray,,2))))
)=1))
Click to expand...

ByRow... perfect... You rock! Thank you so much!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Multiple Layer VLOOKUP, or better alternative?
Replies
9
Views
632
JukeExcel
J
Sufiyan97
  • Solved
VBA Filter Names from List One by One and then Copy filtered data v2
Replies
6
Views
196
Sufiyan97
Sufiyan97
S
  • Question Question
Using output of LAMBDA within a function
Replies
3
Views
197
Phuoc
P
H
  • Solved
Retrieve the intersection of multiple cells with respect to columns and rows
2
Replies
10
Views
165
mumps
mumps
JenniferMurphy
  • Solved
How to count events in a calendar year?
2
Replies
14
Views
690
JenniferMurphy
JenniferMurphy

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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