The DATE function builds dates from separate year, month, and day values. One of its tricks is the ability to roll forward to correct dates when given days and months that are "out of range". For example, DATE returns April 9, 2016, with the following arguments:
...Read more
The foundation of this formula is the MID function, which extracts a specific number of characters from text, starting at a specific location. To figure out where to start extracting text, we use this expression:
SEARCH("(",B5)+1
This...Read more
The SUBSTITUTE function can find and replace text in a cell, wherever it occurs. In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which...Read more
Working from the inside out, the expression C5:G5<>"" returns an array of true and false values:
{FALSE,TRUE,FALSE,FALSE,FALSE}
The number 1 is divided by this array, which creates a new array composed of either 1's or #DIV/0! errors...Read more
In this example, the goal is to sum amounts by weekday. In other words, we want to sum amounts by Monday, Tuesday, Wednesday, and so on. Column B contains valid Excel dates formatted with a custom number format explained below. For convenience, all source data...Read more
Working from the inside out, the MATCH function matches the range against itself. That is, we give the MATCH function the same range for lookup value and lookup array (B5:F5).
Because the lookup value contains more than one value (an array), MATCH returns an array of results, where each...Read more
The goal is to calculate the correct invoice status ("OK", "Paid", or "Overdue") using the following rules:
Dates in Excel are just serial numbers that begin on January 1, 1900. If you enter 1/1/1900 in Excel, and format the result with the "General" number format, you'll see the number 1. This means that you can easily calculate...Read more