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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

JenniferMurphy
  • Solved
How to count events in a calendar year?
2
Replies
14
Views
313
JenniferMurphy
JenniferMurphy
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
229
SaraWitch
SaraWitch
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
793
Dave Patton
D
SaraWitch
  • Question
Highlight cell if text matches in range of cells, but only if other cells are blank
2
Replies
11
Views
316
SaraWitch
SaraWitch
T
  • Solved
Assistance with array formula
Replies
2
Views
316
tigerzen
T

Forum statistics

Threads
1,214,605
Messages
6,120,476
Members
448,967
Latest member
visheshkotha

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