In this example, the goal is to calculate the number of months between two valid Excel dates. This is a curiously tricky problem in Excel because the number of days in a month varies, and the rules about how a whole month might be calculated are not obvious. In...Read more
In this example, the goal is to sum Amounts in column C by state using the two-letter codes in column E. Note the states are abbreviated, "CA" is California, "FL" is Florida, "TX" is Texas, and "WA" is Washington. The challenge in this case is that the state abbreviations are embedded...Read more
The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string ("") for the "new_text" argument.
In this case, we want to remove the labels that appear inside text. The labels vary in length, and...Read more
The SUBSTITUTE function lets you replace text by matching content.
In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle this easily — we just need to provide a cell reference (B6), the text to remove ("-"), and the an...Read more
The replace function lets you replace text based on its location and length. In this case, we want to strip off the drive and path and leave only the document name. The length of this part of the string (text) is 24 and the starting position is 1, and the pattern never changes.
The...Read more
This formula is uses the WORKDAY function, which returns a date in the future or past, based on start date and required work days. WORKDAY automatically excludes weekends, and can also exclude holidays if provided as a range of dates.
In the example shown, the project end date is in...Read more
In this example, the goal is to count the number of cells in column D that contain dates that are between two variable dates in G4 and G5. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below. For convenience, the worksheet contains two...Read more
The MOD function returns the remainder after division. With a divisor of 1, MOD will return zero for any whole number.
We use this fact to construct a simple formula that tests the result of MOD. When the result is zero (i.e. when the number is an integer) the formula returns TRUE,...Read more
The CHOOSE function provides the framework for this formula. Choose takes a single numeric value as its first argument (index_number), and uses this number to select and return one of the values provides as subsequent arguments, based on their numeric index.
In this case, we are using...Read more
In this example, the goal is to join together three text values separated by line breaks. In Excel, you can use the keyboard shortcut Alt + Enter to add a line break in a cell that contains text, but the same approach won't work in a formula....Read more
