Can Excel find info between two numbers or dates?

Dman25

New Member
Joined
Aug 7, 2020
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
Example: 18-10 thru 18-25 can it find 18-20 or a date such as 11/25/15 thru 12/25/20 can it find dates between that?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Can you explain more what you are actually trying to achieve, if you just want those dates shown in place you can just use the autofilter using the between option.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
like this?

with Power Query (Get&Transform)
startendCustom
18-1018-2510
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.TransformColumnTypes(Source,{{"start", type text}, {"end", type text}}),
    ETAD = Table.TransformColumns(Text, {{"start", each Text.AfterDelimiter(_, "-"), type text}}),
    ETAD2 = Table.TransformColumns(ETAD, {{"end", each Text.AfterDelimiter(_, "-"), type text}}),
    Number = Table.TransformColumnTypes(ETAD2,{{"start", Int64.Type}, {"end", Int64.Type}}),
    List = Table.AddColumn(Number, "Custom", each {[start]..[end]}),
    Serie = Table.ExpandListColumn(List, "Custom"),
    TSC = Table.SelectColumns(Serie,{"Custom"})
in
    TSC
or like this

serie.png


for dates is a similar method
 

Dman25

New Member
Joined
Aug 7, 2020
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
I’ve got boxes with records that have the year with the file numbers on the outside of the box. Example year-file #. So it looks like this 18-200 thru 18-250. Also some just have the date ranges example 11/25/18 thru 11/30/19. I want to be able to type in 18-220 and it find it in that cell, or the date 4/10/19.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

post representative example of source and what you want to achieve (more than one row)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
something like that?

NumberDateValueDate
18-20018-22009/01/2019
18-20125/11/2018
18-20226/11/2018
18-20327/11/2018
18-20428/11/2018
18-20529/11/2018
18-20630/11/2018
18-20701/12/2018
18-20802/12/2018
18-209
18-210
18-211
18-21201/01/2019
18-21302/01/2019
18-21403/01/2019
18-21504/01/2019
18-21605/01/2019
18-21706/01/2019
18-21807/01/2019
18-21908/01/2019
18-22009/01/2019
18-22110/01/2019
18-22211/01/2019
18-22312/01/2019
18-22413/01/2019
18-22514/01/2019
18-22615/01/2019
18-22716/01/2019
18-22817/01/2019
18-229
18-230
18-231
18-232
18-233
18-234
18-235
18-236
18-237
18-238
18-239
18-240
18-241
18-24222/11/2019
18-24323/11/2019
18-24424/11/2019
18-24525/11/2019
18-24626/11/2019
18-24727/11/2019
18-24828/11/2019
18-24929/11/2019
18-25030/11/2019

Rich (BB code):
let
    Src1 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Src2 = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Join = Table.NestedJoin(Src2,{"Value"},Src1,{"Number"},"Src1",JoinKind.LeftOuter),
    Exp = Table.ExpandTableColumn(Join, "Src1", {"Date"}, {"Date"}),
    TDate = Table.TransformColumnTypes(Exp,{{"Date", type date}}),
    TSC = Table.SelectColumns(TDate,{"Date"})
in
    TSC
 

Dman25

New Member
Joined
Aug 7, 2020
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
UnitBox #Box content
HR1326files 18-90 thru 20-15
Payroll1890files 14-50 thru 16-100
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
sorry but it doesn't make sense to me
I am not in your head and forgot my Crystal Ball so I don't know what you want to do with data from the post#7

btw. you can use XL2BB to post data here
 

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,996
Members
414,419
Latest member
JRDunya

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
Top