In this example, the goal is to convert the text values seen in column B to the numeric values seen in column D. There are several ways to fix this problem in Excel, but this article focuses on a formula-based approach to convert text values to numbers. It also explains how to...Read more
The key to 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 hardcoded date in D4, you can use =D4+1 to populate the calendar. This allows you to set up a conditional formatting rule that compares the date in...Read more
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:
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
