Excel dates are just serial numbers, so you can calculate durations by subtracting the earlier date from the later date.
This is what happens at the core of the formula here:
MIN(end,C6)-MAX(start,B6)+1
Here are...

The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.
Working from the...

The MAX function accepts one or more arguments, which can be a mix of constants, cell references, and ranges. MAX then returns the maximum value in the data provided. Text values and empty cells are ignored.
In this...

This formula uses the MOD and COLUMN functions to filter out values not in nth columns, then runs MAX on the result.
The key is this snippet:
MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0
Here, the formula uses the COLUMN...

The IF function is evaluated first. The logical test is an expression that tests all names:
IF(names=F6 // logical test
The result is an array of TRUE / FALSE values like this:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;...

In order to understand this problem, make sure you understand how percentage number formatting works. In a nutshell, percentages are decimal values: 0.1 is 10%, 0.2 is 20%, and so on. The number 1, when formatted as a...

The MAX function is fully automatic – it returns the largest value in the numbers provided. In this case, we give MAX function two values:
=MAX(B5,C5)
and MAX returns the larger value.
Elegant alternative to IF...

Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, Excel will treat the numbers in this format as text, and you won't be able to perform math operations on such...

At the core, this formula runs two tests on a value like this:
=D5>MIN(B5,C5) // is D5 greater than smaller?
=D5<MAX(B5,C5)) // is D5 less than larger?
In the first expression, the value is compared to the...

The MINIFS function returns the smallest numeric value that meets supplied criteria, and the MAXIFS function returns the largest numeric value that meets supplied criteria.
Like COUNTIFS and SUMIFS, these functions...

The gist of this formula is that we build a list of row numbers for a given range, matching on a value, and then use the MAX function to get the largest row number , which corresponds to the last matching value. We are...

The key to this formula is the MATCH function, which is set up like this:
MATCH(MAX(LEN(name)),LEN(name),0))
In this snippet, MATCH is set up to perform an exact match by supplying zero for match type. For lookup...

This example uses the following named ranges: "color" = B6:B14, "item" = C6:C14, and "price" = E6:E14. The goal is to find the maximum price for a given color and item.
This formula uses two nested IF functions,...

The core of this formula is the MATCH function, which locates the position of the longest string using supplied criteria:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Note MATCH is set up to perform an...

The MAXIFS function can find the maximum value in a range based on one or more criteria. In the example shown, we are using MAXIFS to find the max sales value based in a given month by "bracketing" dates between the...