Dave Bruns

The TEXT function takes numeric values and converts them to text values using the format you specify. In this example, we are using the format "mmm d" for both TEXT functions in E5. The results are joined with a hyphen using simple concatenation.

Note: the other examples in column E...Read more

The trick with this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format "d". With a static date in D4, you can use =D4+1 to populate the calendar. This makes it easy to set up a...Read more

This is a standard VLOOKUP formula. It requires a table with lookup values (in this case, dates) to the left of the values being retrieved.

The lookup value comes from cell E6, which must be a valid date. The table array is the range B6:C11, and the column index is 2, since the amounts...Read more

Consider for a moment how overlapping dates work. For a project to overlap the dates of other projects, two conditions must be true:

1. The start date must be less than or equal (<=) to at least one other end date and the list.
2. The end date for the project must be
...Read more

The MOD function performs the modulo operation. It takes a number and a divisor and returns the remainder after division.

In this formula, we subtract one MOD result from another.

For the first MOD, we use the number with a divisor that equals the place value we want times 10....Read more

Working from the inside out, the EOMONTH function gets the last day of month of any date. To this result, we add 1, which results in the first day of the next month.

This date goes into WORKDAY function as the "start date", along with -1 for "days". The WORKDAY function automatically...Read more

The core of this formula is the ROUNDUP function. The ROUNDUP function works like the ROUND function except that when rounding, the ROUNDUP function will always round the numbers 1-9 up. In this formula, we use that fact to repeat values.

To supply a number to ROUNDUP, we are using this...Read more

In this example, the context is that a certain number of items are needed and the items are only available in bundles of a set size. The goal is to calculate the number of bundles needed based on the items in each bundle, and the number of items needed. For example:

• If you
...Read more