Dave Bruns

In the image shown, the current date is August 19, 2019.

Excel dates are serial numbers, so you can manipulate them with simple math operations. The TODAY function always returns the current date. Inside the...Read more

There is no direct way to detect a hidden column with a formula in Excel. You might think of using the SUBTOTAL function, but SUBTOTAL only works with vertical ranges. As a result, the approach described in this example is a workaround based on a helper...Read more

Note: this is a great example of a formula that is hard to understand because the cell references are hard to interpret. The gist of the logic used is this: if the time in row 4 is between the start and end times, the formula should return TRUE and trigger the blue fill via conditional...Read more

In this example, the goal is to calculate a monthly mortgage payment based on three inputs:

1. The loan amount
2. The annual interest rate
3. The loan term in years

The worksheet shown also takes into account the down payment, which is calculated using a...Read more

In this example, the goal is to create a formula that will return "Pass" or "Fail" depending on whether a student has a passing score in at least 4 out of 6 subjects. This problem can be easily solved with a formula based on the COUNTIF function...Read more

The goal is to display a checkmark (also called a "tick mark" in British English) when a task is marked complete. The easiest way to do this is with the IF function and the mark you would like to display. The article below explains several options.

IF with a plain checkmark

The...Read more

The dropdown is set up with a simple data validation rule based on a "list":

Red,Blue,Green,All

The named ranges "color" (C5:C15) and "qty" (D5:D15)...Read more

The goal is to extract records with dates that are greater than or equal to a start date in F5 and less than or equal to an end date in G5. You might think we can use the AND function inside FILTER to solve this problem. However, because AND returns...Read more

This example uses the UNIQUE function together with the FILTER function. You can see a more basic example here.

The trick in this case is to apply criteria to the FILTER function to only allow values based on the count of occurrence....Read more

We need a way to split text at a certain marker that corresponds to a certain number of words. Excel doesn't have a built-in function to parse text by word, so are using the SUBSTITUTE function's "instance" argument to replace an "nth space" character with the pound sign (#), then using FIND and...Read more