Excel BETWEEN Formula Trick - Find if a date/value falls between a range!

There's no such thing as a BETWEEN function in Excel, but with this trick we can easily check if a date or value falls within a range.

0:00 Introduction
0:23 Between with AND including limits
0:48 Between with AND excluding limits
1:29 Between with MEDIAN including limits
2:11 Between with MEDIAN excluding limits

[IvanCortinas_ES]

Spectacular trick. Thank you very much Mynda!

[mehul]

Nice trick, thanks for sharing. I think I will stick to AND function as it's easy to use and gives you more flexibility when sometimes you have to include upper limit for certain range and exclude on others, for example, ATO tax rates table.

[patrickschardt]

What a great little trick.

You just gave me an idea for a function called BETWEEN.
It has four arguments:
Value: the number or date you are checking
Lower_limit: the lower limit to check against
Upper_limit: the upper limit to check against
Behavior: the type of limits to use…
0. Include both lower and upper limits
1. Exclude both limits
2. Include lower/ exclude upper
3. Exclude lower/ include upper

The behavior argument would be optional and the default would be inclusive. This function returns TRUE if the value is between the limits and FALSE if it’s not.

Right now this would be for numbers and dates. This could also work for text by converting each letter to the ASCII number equivalent, concatenating them into one big string, and finally comparing the 3 numbers

[darrylmorgan]

Hi Mynda!Really Helpful Super Cool Trick...Thank You :)

[Alexisj]

Hi! thanks for sharing your skills! If I may ask, is there a way for excel to calculate the number of tasks with perfects scores needed to reach a certain goal percentage? Like for example a person did 5 tasks scoring at 95%, 92%, 93%, 98%, 100%. Now given the values you would get an average of 95.6. Now going back to my question, given the context, can excel calculate the number of tasks with perfect scores needed to reach a goal of 98% average? Thank you in advance!

[Excelambda]

MEDIAN is a great trick!!
A single cell form alternative:

[martineyc]

Thanks for your videos. I have a situation say Column A have different Vendor numbers and Column B have different dates and column C have amounts. I need a result that date when sum of amount above zero. Please help me on this.

[SNOOPYDOG]

Goode evening, I need to ask you an information. If in an Excel sheet we have two columns in which different date ranges are highlighted, for example:
14/03/2022 > 25/09/2022
10/05/2022 > 12/12/2022
05/01/2022 > 13/05/2022
Is it possible to identify trought PowerQuery, or some Excel formulas, which columns definitely include, 100%, a specific type date range: 05/04/2022 > 28/04/2022? Thanks in advance so much.

[ifeoluwaojeleye]

Hello,

Can you point me to your video on how to set up customer ledger?

Thanks

[dispirted]

I never use MEDIAN at all but this looks like a fun application so maybe I will try it

[oburukennth]

Hi Treacy, I recently increased commission for sales of my products and I use tabulated data to track payments, how can I maintain the previous amount and the current amount in the same data table, Thank you in advance. We appreciate your tutorials a lot

[peterbartholomew]

Hey Mynda, where's the version with the Lambda function? Maybe
= BYROW(data, Betweenλ)
where
Betweenλ = LAMBDA(v, MEDIAN(v)=TAKE(v, , 1) )

[krishnamanjunatha]

A25D110C12 - plz provide formula to split at each range in each cell in horizontal like cell1 - A25, cell2 - D110, cell3 - C12

[vvfsbub]

Thanks for this. I'm wondering if you can help with something I am working on. I want to check the unit price of an item at a given time. Any input is appreciated. thanks
Sheet A
Date Item Unit Price
01 Jan 2023 Apple ?
12 Nov 2022 Apple ?
11 Jan 2023 Orange ?
23 Nov 2022 Orange ?

Sheet B
Item Date from Date to Unit Price
Apple 1 Jan 2023 31 Jan 2023 0.50
Apple 1 Dec 2022 31 Dec 2022 0.45
Apple 1 Nov 2022 30 Nov 2022 0.40
Orange 1 Jan 2023 31 Jan 2023 0.30
Orange 1 Dec 2022 31 Dec 2022 0.31
Orange 1 Nov 2022 30 Nov 2022 0.29

[davidferrick]

One step further with a named LAMBDA and you have a BETWEEN function.

[eidmohamedali]

The LAMBDA function is the easier way to create a personalized BETWEEN function

[flyingHubby]

nice trick, but not intuitive. So anyone else than me, the author, would not easily understand it's meaning.