Dave Bruns

In this example, the goal is to count numbers in the range B5:B15 (named data) that begin with the numbers shown in column D. You would think this would be a good problem to solve with the COUNTIF function but for reasons explained below,...Read more

In this example if a task is marked "Done", then it is considered complete. The goal is to calculate the percent complete for the project by showing the ratio of complete tasks to total tasks, expressed as a percentage. The formula in F6 is:

=COUNTA(C5:C11)/...Read more

The ISREF function returns TRUE for a valid worksheet reference and FALSE is not.

In this case, we want to find out of a particular sheet exists in a workbook, so we construct a full reference by concatenating the sheet names in column B with an exclamation mark and "A1":

...Read more

The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period (".").

=LEFT(filename,characters)

The FIND function is used to figure out...Read more

The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as follows:

1. The year value is extracted with the RIGHT function:...Read more

Working from the inside out, we use the DATEDIF function to calculate how many complete years are between the original anniversary date and the "as of" date, where the as of date is any date after the anniversary date:

DATEDIF(B5,C5,"y")
...Read more

In Excel, dates are simply serial numbers. In the standard date system for windows, based on the year 1900, where January 1, 1900 is the number 1. This means that January 1, 2050 is the serial number 54,789.

• If you are calculating a date n days in the future, you can add days
...Read more

In this example, the goal is to calculate the time remaining before an expiration date. There are many ways to do something like this in Excel, and in this article, we'll look at three different approaches:

1. Calculating the remaining time in days
2. Calculating the remaining
...Read more

Working from the inside out, we use a standard INDEX and MATCH function to locate the first match of lookup values in column B:

INDEX(data,MATCH(B5,data,0))

The MATCH function gets the position of the value in B5 inside the named range data...Read more

Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, cell B3 in this case.

To highlight matching rows, we use this logical expression:

$B4=$K$5

The reference...Read more