8. Using Everyday Functions: Math, Date and Time, and Text Functions

Excel offers many functions for dealing with basic math, dates and times, and text. This chapter describes the functions that you can access with the Formulas tab using the Text icon, the Date & Time icon, and the Math portion of the Math & Trig icon.

The SUM function adds all the numbers in a range of cells. The arguments number1, number2,... are 1 to 255 arguments for which you want the total value or sum.

A typical use of this function is =SUM(B4:B12). It is also possible to use =SUM(1,2,3). In the latter example, you cannot specify more than 255 individual values. In the former example, you can specify up to 255 ranges, each of which can include thousands or millions of cells.

In Figure 8.1, cell B25 contains a formula to sum three individual cells: =SUM(B17,B19,B23).

It is unlikely that you will need more than 255 arguments in this function, but if you do, you can group arguments in parentheses. For example, =SUM((A10,A12),(A14,A16)) would count as only two of the 255 allowed arguments.

If a text value that looks like a number is included in a range, the text value is not included in the result of the sum. Strangely enough, if you specify the text value directly as an argument in the function, Excel adds it to the result. For example, =SUM(1,2,"3") is 6, yet =SUM(D4:D6) in cell D7 of Figure 8.1 results in 3.

The comma is treated as a union operator. If you replace the comma with a space, Excel finds the cells that fall in the intersection of the selected ranges. In cell E17, the formula of =SUM(F13:H14 G12:G15) adds up the two cells that are in common between the two ranges.

If one cell in a referenced range contains an error, the result of the SUM function is an error. To add numbers while ignoring error cells, use the AGGREGATE function.

It is valid to create a spearing formula. This type of formula adds the identical cell from many worksheets. For example, =SUM(Jan:Dec!B20) adds cell B20 on all 12 sheets between Jan and Dec. If the sheet names contain spaces or other nonalphabetic characters, surround the sheet names with apostrophes: =SUM('Jan 2018:Dec 2018'!B20).

=AGGREGATE(function_num, options, array, [k])

The options argument is the interesting feature of the function. You can choose to ignore any, all, or none of these categories:

Error values

Hidden rows

Other SUBTOTAL and AGGREGATE functions

On one hand, the capability to ignore filtered rows and other AGGREGATE functions is similar to the SUBTOTAL function. The capability of AGGREGATE to ignore error values solves a common Excel problem. For most Excel functions, a single #N/A error cell in a range causes most functions to return an #N/A error. The options in AGGREGATE enable you to ignore any error cells in the range.

The options argument controls which values are ignored. This is a simple binary system, as follows:

To ignore other subtotals, add 0. To include subtotals, add 4.

To ignore hidden rows, add 1.

To ignore error values, add 2.

Thus, to ignore other subtotals, hidden rows, and error values, you specify 3 (0+1+2) as the options argument.

To ignore error values but include other SUBTOTAL values, you specify 5 (1+4) as the argument.

This calculation works out as shown in Table 8.4.

In Figure 8.2, the #N/A error in cell F13 causes the SUM function in F18 to also return an #N/A. If you use a 2, 3, 5, or 7 as the second argument of AGGREGATE, you can easily sum all the other numbers as in cell F1. You can also use other function numbers to calculate MIN, MAX, COUNT, MEDIAN, MODE, PERCENTILE, and QUARTILE values.

You can also use the function to ignore cells hidden by a filter. Whereas the old SUBTOTAL function enabled you to do this for 11 calculation functions, the AGGREGATE function adds 8 new functions to the list.

Table 8.5 shows the 19 functions available in the AGGREGATE function. This list mirrors the 11 functions available in SUBTOTAL (arranged alphabetically to match those in the SUBTOTAL function) and then 8 new functions arranged in order of popularity.

The last six functions in this list require you to specify a value for k as the fourth argument. LARGE and SMALL typically return the kth largest or smallest value from a list. Use the fourth argument in AGGREGATE to specify the value for k. The last six functions allow for a calculated array instead of a range of cells.

In cell F3 of Figure 8.2, the final argument of 3 specifies that you want the third smallest number in the array. For LARGE, SMALL, and QUARTILE, you should specify an integer for k. For PERCENTILE, specify a decimal between 0 and 1.

When you are trying to return results from the visible rows of a filtered data set, you can use either SUBTOTAL or AGGREGATE. In Figure 8.3, the SUM function in D1 returns the sum of the visible and hidden rows. The SUBTOTAL function in D2 returns the sum of the visible rows, the same as the AGGREGATE function in D3. The advantage of AGGREGATE is that it can return MEDIAN, LARGE, SMALL, PERCENTILE, and QUARTILE on the visible rows as well.

ROUND(number, num_digits) rounds the number. To round to the nearest dollar, use 0 as the second argument. To round to the nearest penny, use 2 as the second argument. To round to the nearest thousand dollars, use –3 as the third argument.

ROUNDUP(number, num_digits) always rounds away from zero. Although this usually makes the number larger, the behavior for negative numbers is unusual. =ROUNDUP(-1.1,0) rounds away from zero to –2. If you want that to round to –1, use TRUNC(number) instead.

ROUNDDOWN(number, num_digits) always rounds toward zero. Although this makes sense for positive numbers, the result for negative numbers might not make sense. =ROUNDDOWN(-3.1,0) rounds toward zero and produces –3. If you expect this to produce –4, use =INT(-3.1) instead.

MROUND(number, multiple) rounds to the nearest multiple. Use for rounding to the nearest 5 or 25. =MROUND(115,25) rounds to 125. There are some unusual variants. =EVEN(number) always rounds up to an even number, for the unusual situation in which items are packed two to a case. =ODD(number) rounds up to an odd integer.

Figure 8.5 illustrates several rounding options.

The last four functions in this group—CEILING, CEILING.MATH, FLOOR, and FLOOR.MATH—round a number in a certain direction to a certain number of digits. They require you to enter the number and the number of decimals to which to round. The behavior of the functions when a number was negative caused complaints from the mathematics community, so the Excel team reversed the behavior with the .MATH versions of the functions.

For example, =CEILING(5.1,1) rounds the 5.1 up to 6. Originally, Excel would always round away from zero: =CEILING(-5.1,-1) would round to –6. Mathematicians pointed out that –6 is actually lower than 5.1 and the correct answer should be –5. Thus, CEILING.MATH(-5.1,1) rounds up to –5.

The older CEILING function required the second argument to have the same sign as number, which was a pain to handle. The .MATH versions can deal with a negative number and a positive significance. Microsoft added an optional third Mode argument that allows CEILING.MATH to round away from zero. Figure 8.6 illustrates CEILING.

It becomes incredibly difficult to total the data when it has intermediate SUM functions. The original formula in C77 must point to each subtotal cell.

Many accountants can teach you the old accounting trick whereby you total the entire column and divide by two to get the grand total. This is based on the assumption that every dollar is in the column twice: once on the detail row and once on the summary row. The formula in D77 is far shorter than the formula in C77 and produces the same answer. This trick does work, but it is hard to explain to your manager why it works.

A better solution is to use the SUBTOTAL function. Instead of =SUM(D2:D75), use =SUBTOTAL(9,D2:D75). The function totals all numbers in D2:D75 but ignores other subtotal functions.

While you are summing in this case, the SUBTOTAL function offers 11 arguments, numbered from 1 to 11: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, and VARP. It just happens that SUM is the ninth item in this list when these functions are arranged alphabetically in the English language, so 9 became the function number for SUM.

The RAND function returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated.

=RAND() generates a random decimal between 0 and 0.999999999999999. Whether you are a teacher trying to randomly assign the order for book report presentations or the commissioner of a fantasy football league trying to figure out the draft sequence, =RAND() can help.

To generate a random number greater than or equal to 0 but less than 100, you can use RAND()*100.

To generate a random sequence for a list, you select a blank column next to your data and enter =RAND() in the column. Every time you press the F9 key, the column generates a new set of random numbers. You might want to agree up front with the draft participants that you will press F9 three times to randomize the list and then convert the formulas to values. To do so, follow these steps:

1. Enter the heading Random in row 1 next to your data.

2. Enter =RAND() in cell B2.

3. Move the cell pointer to cell B2 and double-click the fill handle.

4. Turn off automatic calculation by using Formulas, Calculation Options, Manual. This prevents the RAND() functions from recalculating after you sort in step 7.

5. Press the F9 key three times.

6. Choose one cell in column B.

7. From the Data tab, click the AZ button to sort ascending. The new sequence of items in column A is random (see Figure 8.9).

You can also use this technique to select a random subset from a data set. If your manager wants you to contact every 20th customer, you can select all the customers in which =RAND() is 0.05 or less.

Whereas =RAND() returns a random decimal, =RANDBETWEEN generates an integer between two integers.

The RANDBETWEEN function returns a random integer between the numbers you specify. A new random number is returned every time the worksheet is calculated. This function takes the following arguments:

bottom—This is the smallest integer RANDBETWEEN can return.

top—This is the largest integer RANDBETWEEN can return.

To generate random numbers between 50 and 59, inclusive, you use =RANDBETWEEN(50,59). RANDBETWEEN is easier to use than =RAND to achieve random integers; with =RAND, you would have to use =INT(RAND()*10)+50 to generate this same range of data.

Even though RANDBETWEEN generates integers, you can use it to generate sales prices or even letters. =RANDBETWEEN(5000,9900)/100 generates random prices between $50.00 and $99.00, including prices with cents, such as $76.54.

The capital letter A is also known as character 65 in the ASCII character set. B is 66, C is 67, and so on up through Z, which is character 90. You can use =CHAR(RANDBETWEEN(65,90)) to generate random capital letters.

As of Excel 2013, the =ARABIC() function can convert a Roman numeral back to a regular number. Whereas =ROMAN()works only with the numbers 1 through 3,999, the ARABIC function deals with invalid Roman numerals from –255,000 through 255,000. Leviculus!

The ABS function measures the size of the error. Positive errors are reported as positive, and negative errors are reported as positive as well. You can use =ABS(A2-B2) to demonstrate that the other station’s forecaster is off by 20 degrees on average.

If you are in seventh grade or you are assisting a seventh grader with his or her math lesson, you will be happy to know that Excel can calculate these values for you.

The GCD function returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.

The arguments number1, number2,... are 1 to 255 values. If any value is not an integer, it is truncated. If any argument is nonnumeric, GCD returns a #VALUE! error. If any argument is less than zero, GCD returns a #NUM! error. The number 1 divides any value evenly. A prime number has only itself and 1 as even divisors.

The LCM function returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments—number1, number2, and so on. You use LCM to add fractions with different denominators.

The arguments number1, number2,... are one to 255 values for which you want the least common multiple. If the value is not an integer, it is truncated. If any argument is nonnumeric, LCM returns a #VALUE! error. If any argument is less than 1, LCM returns a #NUM! error.

Using the MOD function is a great way to perform this concept with records in a database. Perhaps for auditing, you need to check every eighth invoice. Or you need to break up a list of employees into four groups. You can solve these types of problems by using the MOD function.

Think back to when you were first learning division. If you had to divide 43 by 4, you would have written that the answer was 10 with a remainder of 3. If you divide 40 by 4, the answer is 10 with a remainder of 0.

The MOD function divides one number by another and reports back just the remainder portion of the result. You end up with an even distribution of remainders. If you convert the formulas into values and sort, your data is broken into similar-size groups.

The MOD function returns the remainder after number is divided by divisor. The result has the same sign as divisor. This function takes the following arguments:

number—This is the number for which you want to find the remainder.

divisor—This is the number by which you want to divide number. If divisor is 0, MOD returns a #DIV/0! error.

The MOD function is good for classifying records that follow a certain order. For example, the SmartArt gallery contains 84 icons arranged with 4 icons per row. To find the column for the 38th icon, use =MOD(38,4).

The example in Figure 8.12 assigns all employees to one of four groups.

A square root is a number that, when multiplied by itself, leads to a square. For example, the square root of 25 is 5, and the square root of 49 is 7. Some square roots are more difficult to calculate. The square root of 8 is a number between 2 and 3—somewhere close to 2.828. You can calculate the number with =SQRT(8).

A related function is the POWER function. If you want to write the shorthand for 6×6×6×6×6, you would say “six to the fifth power,” or 6⁵. Excel can calculate this with =POWER(6,5).

If you multiply 5×5×5 to get 125, then the third root of 125 is 5. The fourth root of 625 is 5. Even a $30 calculator offers a key to generate various roots beyond a square root. Excel does not offer a cube root function. In reality, even the POWER and the SQRT functions are not necessary.

=6^3 is 6 raised to the third power, which is 6×6×6, or 216.

=2^8 is 2 to the eighth power, which is 2×2×2×2×2×2×2×2, or 256.

For roots, you can raise a number to a fractional power:

=256^(1/8) is the eighth root of 256. This is 2.

=125^(1/3) is the third root of 125. This is 5.

Thus, instead of using =SQRT(25), you could just as easily use =25^(1/2). However, people reading your worksheets are more likely to understand =SQRT(25) than =25^(1/2).

Figure 8.13 shows a database that contains thousands of records. Your goal is to find out how many records came from each region. One way to write the formula for the East region is =COUNTIF($C$11:$C$5011,"East"). However, it is far more interesting to write the formula as shown in cell B2:

=COUNTIF($C$11:$C$5011,A2)

After you enter this formula, you can build a table of the unique regions in column A, copy the formula down column B, and quickly have a summary table built with the help of COUNTIF.

The COUNTIF function counts the number of cells within a range that meet the given criteria. This function takes the following arguments:

range—This is the range of cells from which you want to count cells.

criteria—This is the criteria in the form of a number, an expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", or "apples". Any criteria that contains text or a mathematical operator must be enclosed in quotes. For numeric criteria, the quotes are not required.

You can use the wildcard characters question mark (?) and asterisk (*) in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, you need to type a tilde (~) before the character.

After you have mastered COUNTIF, it is easy to master SUMIF and AVERAGEIF. In most cases, the SUMIF function adds one new argument. Whereas COUNTIF would ask for a range of data and then the value to look for in that range, SUMIF usually needs three arguments: SUMIF asks for a range of data, the value to look for in that range, and then another range of data to be summed when a match is found.

In Figure 8.13, B11:B5011 contains the range to search. Cell A2 contains the value for which to search. When Excel finds a matching value in column B, you want Excel to return the corresponding cell from the Revenue column in H11:H5011. Most people would write =SUMIF($C$11:$C$5011,A2,$H$11:H$5011) to do this. It turns out that Excel forces the third argument to have the same shape as the first argument. If you happen to accidentally specify H11:H4011, Excel ignores your range and uses H11:H5011 because it is the same shape as the first argument. Thus, it is sufficient to write the formula as =SUMIF($C$11:$C$5011,A2,$H$11).

Mastering the SUMIF and COUNTIF functions invariably leads to more questions about doing more powerful versions. If you need to sum based on more than one condition, you can use DSUM, SUMPRODUCT, or SUMIFS. The SUMIFS function is discussed in the next section.

=SUMIF (range,criteria,sum_range)

=AVERAGEIF (range,criteria,average_range)

The SUMIF function adds the cells specified by a given criteria. The AVERAGEIF function averages the cells specified by a given criteria. Occasionally, the range you want to search is also the range to sum. For example, perhaps your criteria is to look for rows in which the revenue is greater than 100,000. In this case, because your range to add is the same as your range to search, you can leave off the third argument, as shown in cell H2 of Figure 8.13.

The SUMIF function takes the following arguments:

range—This is the range of cells you want evaluated.

criteria—This is the criteria in the form of a number, an expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", or "apples".

sum_range—This is the range of cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.

Thankfully, Excel 2007 added plural versions of SUMIF(), COUNTIF(), and AVERAGEIF() that can handle not just two conditions, but up to 127 conditions. The three new functions add the letter S to the end of the function name (that is, SUMIFS(), COUNTIFS(), and AVERAGEIFS()) to signify that multiple IFs are being considered. With SUMIFS() and AVERAGEIFS(), you first specify the range to be summed or averaged. You then specify pairs of arguments. In each pair, you first specify the range to check and then the value to match in that range. The following sections describe these three functions.

SUMIFS(sum_range,criteria_range1,criteria1[,criteria_range2, criteria2...])

The SUMIFS() function adds the cells in a range that meet multiple criteria.

Note the following in this syntax:

sum_range is the range to sum.

criteria_range1, criteria_range2, ... are one or more ranges in which to evaluate the associated criteria.

criteria1, criteria2, ... are one or more criteria in the form of a number, an expression, a cell reference, or text that define which cells will be added. For example, they can be expressed as 32, "32", ">32", "apples", or B4.

Each cell in sum_range is summed only if all the corresponding criteria specified are true for that cell.

Cells in sum_range that contain TRUE evaluate to 1; cells in sum_range that contain FALSE evaluate to 0.

You can use the wildcard characters question mark (?) and asterisk (*) in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, you need to type a tilde (~) before the character.

Unlike the range and criteria arguments in SUMIF, the size and shape of each criteria_range and sum_range must be the same.

In Figure 8.14, you want to build a table that shows the total by region and product. sum_range is the revenue in H11:H5011. The first criteria pair consists of the regions in $C$11:$C$5011 being compared to the word East in B$1. The second criteria pair consists of the divisions in $B$11:$B$5011 being compared to G854 in $A2. The formula in B2 is =SUMIFS($H$11:$H$5011,$C$11:$C$5011,B$1,$B$11:$B$5011,$A2). You can copy this formula to B2:D6.

Here is why dates are a problem. First, Excel stores dates as the number of days since January 1, 1900. For example, June 30, 2018, is 43,281 days since 1/1/1900. When you enter 6/30/2018 in a cell, Excel secretly converts this entry to 43,281 and formats the cell to display a date instead of the value. So far, so good. The problem arises when you try to calculate something based on the date.

When you try to perform a calculation on two cells when the first cell is formatted as currency and the second cell is formatted as fixed numeric with three decimals, Excel has to decide if the new cell inherits the currency format or the fixed with three decimals format. These rules are hard to figure out. In any given instance, you might get the currency format or the fixed with three decimals format, or you might get the format previously assigned to the cell with the new formula. With numbers, a result of $80.52 or 80.521 looks about the same. You can probably understand either format.

However, imagine that one of the cells is formatted as a date. Another cell contains the number 30. If you add the 30 to the date, which format does Excel use? If the cell containing the new formula happened to be previously assigned a numeric format, the answer suddenly switches from a date format to the numeric equivalent. This is frustrating and confusing. You start with June 30, 2018, add 30 days, and get an answer of 43,311. This makes no sense to an Excel novice. It forces many people to give up on dates and start storing dates as text that looks like dates. This is unfortunate because you can’t easily do calculations on text cells that look like dates.

Here is a general guideline to remember: If you work with dates in the range of the years 2010 to 2025, those numeric equivalents are from 40,179 through 46,022. If you do some date math and get a strange answer in the 40,000–50,000 range, Excel probably has the right answer, but the numeric format of the answer cell is wrong. You need to select Date from the Number drop-down on the Home tab to correct the format.

The Excel method for storing dates is simple when you understand it. If you have a date cell and need to add 15 days to it, you add the number 15 to the cell. Every day is equivalent to the number 1, and every week is equivalent to the number 7. This is very simple to understand.

When you see 43,281 instead of June 30, 2018, Excel calls the 43,281 a serial number. Some of the Excel functions discussed here convert from a serial number to text that looks like a date, or vice versa.

For time, Excel adds a decimal to the serial number. There are 24 hours in a day. The serial number for 6:00 a.m. is 0.25. The serial number for noon is 0.5. The serial number for 6:00 p.m. is 0.75. The serial number for 3:00 p.m. on June 30, 2015 is 43,281.625. To see how this works, try this out:

1. Open a blank Excel workbook.

2. In any cell, enter a number in the range of 40,000 to 45,000.

3. Add a decimal point and any random digits after the decimal.

4. Select that cell.

5. From the Home tab, select the dialog launcher in the lower-right corner of the Number group.

6. In the Date category, scroll down and select the format 3/14/01 1:30 PM. Excel displays your random number as a date and time. If the decimal portion of your number is greater than 0.5, the result is in the p.m. portion of the day.

7. Go to another cell and enter the date you were born, using a four-digit year.

8. Again select the cell and format it as a number. Excel converts it to show how many days after the start of the last century you were born. This is great trivia but not necessarily useful.

The point is that Excel dates are nothing to be afraid of. You need to understand that behind the scenes, Excel is storing your dates as serial numbers and your times as decimal serial numbers. Occasionally, circumstances cause a date to be displayed as a serial number. Although this freaks some people out, it is easy to fix using the Format Cells dialog. Other times, when you want the serial number (for example, to calculate elapsed days between two dates), Excel converts the serial number to a date, indicating, for example, that an invoice is past due by “February 15 1900” days. When you get these types of non sequiturs, you can visit the Format Cells dialog.

mm—Displays the month with two digits. Months before October are displayed with a leading zero (for example, January is 01).

m—Displays the month with one or two digits, as necessary.

mmm—Displays a three-letter abbreviation for the month (for example, Jan, Feb).

mmmm—Spells out the month (for example, January, February).

mmmmm—First letter of the month, useful for creating “JFMAMJJASOND” chart labels.

dd—Displays the day of the month with two digits. Dates earlier than the 10th of the month are displayed with a leading zero (for example, the 1st is 01).

d—Displays the day of the month with one or two digits, as needed.

ddd—Displays a three-letter abbreviation for the name of the weekday (for example, Mon, Tue).

dddd—Spells out the name of the weekday (for example, Monday, Tuesday).

yy or y—Uses two digits for the year (for example, 15).

yyyy or yyy—Uses four digits for the year (for example, 2015).

You are allowed to string together any combination of these codes with a space, comma, slash, or dash. It is valid to repeat a portion of the date format. For example, the format dddd, mmmm d, yyyy shows the day portion twice in the date and would display as Thursday, March 5, 2018.

Although the date formats are mostly intuitive, several difficulties exist in the time formats. The first problem is the M code. Excel has already used M to mean month. In a time format, you cannot use M alone to mean minutes. The M code must either be preceded or followed by a colon.

There is another difficulty: When you are dealing with years, months, and days, it is often perfectly valid to mention only one of the portions of the date without the other two. It is common to hear any of these statements:

“I was born in 1965.”

“I am going on vacation in July.”

“I will be back on the 27th.”

If you have a date such as March 5, 2018 and use the proper formatting code, Excel happily tells you that this date is March or 2018 or the 5th. Technically, Excel is leaving out some really important information—the 5th of what? As humans, we can often figure out that this probably means the 5th of the next month. Thus, we aren’t shocked that Excel is leaving off the fact that it is March 2018.

Imagine how strange it would be if Excel did this with regular numbers. Suppose you have the number 352. Would Excel ever offer a numeric format that would display just the tens portion of the number? If you put 352 in a cell, would Excel display 5 or 50? It would make no sense.

Excel treats time as an extension of dates and is happy to show you only a portion of the time. This can cause great confusion. To Excel, 40 hours really means 1 day and 16 hours. If you create a timesheet in Excel and format the total hours for the week as H:MM, Excel thinks that you are purposefully leaving off the day portion of the format! Excel presents 45 hours as just 21 hours because it assumes you can figure out there is 1 day from the context. But our brains don’t work that way; 21 hours means 21 hours, not 1 day and 21 hours.

To overcome this problem in Excel, you use square brackets. Surrounding any time element with square brackets tells Excel to include all greater time/date elements in that one element, as in the following examples:

5 days and 10 hours in [H] format would be 130.

5 days and 10 hours in [M] format would be 7800, to represent that many minutes.

5 days and 10 hours in [S] format would be 468000, to represent that many seconds.

As shown in Figure 8.16, the time formatting codes include various combinations of h, hh, s, ss, :mm, and mm:, all of which can be modified with square brackets.

To display date and time, you enter the custom date format code, a space, and then the time format code.

Excel offers two functions for calculating the current date: NOW and TODAY. These functions are excellent for figuring out the number of days until a deadline or how late an open receivable might be.

NOW returns the serial number of the current date and time. TODAY returns the serial number of the current date. The TODAY function returns today’s date, without a time attached. The NOW function returns the current date and time.

Both of these functions can be made to display the current date, but there is an important distinction when you are performing calculations with the functions. In Figure 8.17, column A contains NOW functions, and column C contains TODAY functions. Row 2 is formatted as a date and time. Row 3 is formatted as a date. Row 4 is formatted as numeric. Cell A3 and C3 look the same. If you need to display the date without using it in a calculation, NOW or TODAY work fine.

Row 8 calculates the number of days until a deadline approaches. Although most people would say that tomorrow is one day away, the formula in A8 would tend to say that the deadline is 0.1277 days away. This can be deceiving. If you are going to use the result of NOW or TODAY in a date calculation, you should use TODAY to prevent Excel from reporting fractional days. The formula in A8 is =A7-A3, formatted as numeric instead of a date.

In Figure 8.18, cell A1 contains a date and time. Functions in A3 through A8 break out the date into components:

=YEAR(date) returns the year portion as a four-digit year.

=MONTH(date) returns the month number, from 1 through 12.

=DAY(date) returns the day of the month, from 1 through 31.

=HOUR(date) returns the hour, from 0 to 23.

=MINUTE(date) returns the minute, from 0 to 59.

=SECOND(date) returns the second, from 0 to 59.

In each case, date must contain a valid Excel serial number for a date. The cell containing the date serial number may be formatted as a date or as a number.

The DATE function returns the serial number that represents a particular date. This function takes the following arguments:

year—This argument can be one to four digits. If year is between 0 and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, =DATE(100,1,2) returns January 2, 2000 (1900+100). If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, =DATE(2000,1,2) returns January 2, 2000. If year is less than 0 or is 10,000 or greater, Excel returns a #NUM! error.

month—This is a number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. For example, =DATE(1998,14,2) returns the serial number representing February 2, 1999. If zero, it represents December of the previous year. If negative, returns prior months, although –1 represents November, –2 is October, and so on.

day—This is a number representing the day of the month. If day is greater than the number of days in the month specified, it adds that number of days to the first day in the month. For example, =DATE(2018,1,35) returns the serial number representing February 4, 2018. Zero represents the last day of the previous month. Negative numbers return days earlier, just as with month. In a trivial example, =DATE(2018,3,5) returns March 5, 2018.

The true power in the DATE function occurs when one or more of the year, month, or day are calculated values. Here are some examples:

If cell A2 contains an invoice date and you want to calculate the day one month later, you use =DATE(Year(A2),Month(A2)+1,Day(A2)).

To calculate the beginning of the month, you use =DATE(Year(A2),Month(A2),1).

To calculate the end of the month, you use =DATE(Year(A2),Month(A2)+1,1)–1.

The DATE function is amazing because it enables Excel to deal perfectly with invalid dates. If your calculations for month cause it to exceed 12, this is no problem. For example, if you ask Excel to calculate =DATE(2018,16,45), Excel considers the 16th month of 2018 to be April 2019. To find the 45th day of April 2018, Excel moves ahead to May 15, 2018.

Figure 8.19 shows various results of the DATE and TIME functions.

The TIME function returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 to 0. 999988425925926, representing the times from 0:00:00 (12:00:00 a.m.) to 23:59:59 (11:59:59 p.m.). This function takes the following arguments:

hour—This is a number from 0 to 23, representing the hour.

minute—This is a number from 0 to 59, representing the minute.

second—This is a number from 0 to 59, representing the second.

As with the DATE function, Excel can handle situations in which the minute or second argument calculates to more than 60. For example, =TIME(12,72,120) evaluates to 1:14 p.m.

Additional examples of TIME are shown in the bottom half of Figure 8.19.

If your dates are in many conceivable formats, you can use the DATEVALUE function to convert the text dates to serial numbers, which can then be formatted as dates.

The DATEVALUE function returns the serial number of the date represented by date_text. You use DATEVALUE to convert a date represented by text to a serial number. The argument date_text is text that represents a date in an Excel date format. For example, "3/5/2018" and "05-Mar-2018" are text strings within quotation marks that represent dates. Using the default date system in Excel for Windows, date_text must represent a date from January 1, 1900, to December 31, 9999. DATEVALUE returns a #VALUE! error if date_text is out of this range. If the year portion of date_text is omitted, DATEVALUE uses the current year from your computer’s built-in clock. Time information in date_text is ignored.

Any of the text values in column A of Figure 8.20 are successfully translated to a date serial number. In this instance, Excel should have been smart enough to automatically format the resulting cells as dates. By default, the cells are formatted as numeric. This leads many people to believe that DATEVALUE doesn’t work. You have to apply a date format to achieve the desired result.

The TIMEVALUE function returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 to 0. 999988425925926, representing the times from 0:00:00 (12:00:00 a.m.) to 23:59:59 (11:59:59 p.m.). The argument time_text is a text string that represents a time in any one of the Microsoft Excel time formats. For example, "6:45 PM" and "18:45" are text strings within quotation marks that represent time. Date information in time_text is ignored.

The TIMEVALUE function is difficult to use because it is easy for a person to enter the wrong formats. In Figure 8.21, many people would interpret cell A8 as meaning 45 minutes and 30 seconds. Excel, however, treats this as 45 hours and 30 minutes. This misinterpretation makes TIMEVALUE almost useless for a column of cells that contain a text representation of minute and seconds.

Frustratingly, Excel does not automatically format the results of this function as a time. Column B shows the result as Excel presents it. Column C shows the same result after a time format has been applied.

=WEEKDAY(serial_number,return_type)

The WEEKDAY function returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. This function takes the following arguments:

serial_number—This is a sequential number that represents the date of the day you are trying to find. Dates may be entered as text strings within quotation marks (for example, "1/30/2018", "2018/01/30"), as serial numbers (for example, 43130, which represents January 30, 2018), or as results of other formulas or functions (for example, DATEVALUE("1/30/2018")).

return_type—This is a number that determines the type of return value:

If return_type is 1 or omitted, WEEKDAY works like the calendar on your wall. Typically, calendars are printed with Sunday on the left and Saturday on the right. The default version of WEEKDAY numbers these columns from 1 through 7.

If return_type is 2, you are using the biblical version of WEEKDAY. In the biblical version, Sunday is the seventh day. Working backward, Monday must occupy the 1 position.

If return_type is 3, you are using the accounting version of WEEKDAY. In this version, Monday is assigned a value of 0, followed by 1 for Tuesday, and so on. This version makes it very easy to group records by week. If cell A2 contains a date, then A2-WEEKDAY(A2,3) converts the date to the Monday that starts the week.

return_types of 11 through 17 were added in Excel 2010. 11 returns Monday as 1 and Sunday as 7 (the same as using 2). 12 returns Tuesday as 1, 13 returns Wednesday as 1, and so on, up to 17 returning Sunday as 1.

Figure 8.22 shows the results of WEEKDAY for all 10 return types.

In the ANSI system, you might have Week 1 actually starting as early as December 29 or as late as January 4. The last week of the year is numbered 52 in most years but is 53 every fourth year. This system ensures that a year is made up of whole seven-day weeks. This is better than the old results of WEEKNUM.

In the old system with WEEKNUM, the week containing the first of the year was always labeled as Week 1. If the first fell on a Sunday, and your weeks started on Monday, then Sunday, January 1 is Week 1 and Monday, January 2 is Week 2. The possibility of having weeks that last for one day made it difficult to compare one week to the next. Nonetheless, the Excel team added new return_types for this system as well. In the past, 1 meant weeks started on Sunday and 2 meant weeks started on Monday. Now, you can specify weeks should start on Monday (11), Tuesday (12), and so on, up to Sunday (17).

=WEEKNUM(serial_num,[return_type])

The WEEKNUM function returns a number that indicates where the week falls numerically within a year. This function takes the following arguments:

serial_num—This is a date within the week.

return_type—This is a number that determines on what day the week begins. The default is 1. If return_type is 1 or omitted, the week begins on Sunday. If return_type is 2, the week begins on Monday. return_types of 11 through 17 were added to Excel 2013 and specify that the week should start on Monday (11) through Sunday (17). The new return_type of 21 ensures that every week has exactly 7 days. Weeks always start on Monday, but the first Thursday of the year is the middle of Week 1.

=DATEDIF(start_date,end_date,unit)

The DATEDIF function calculates complete years, months, or days. This function calculates the number of days, months, or years between two dates. It is provided for compatibility with Lotus 1-2-3. This function takes the following arguments:

start_date—This is a date that represents the first, or starting, date of the period.

end_date—This is a date that represents the last, or ending, date of the period.

unit—This is the type of information you want returned. The various values for unit are shown in Table 8.6.

Figure 8.23 compares the six unit values of DATEDIF. Each cell uses $A$1 as the start date and that row’s column A as the end date.

=DAYS(end_date, start_date,)

The DAYS function always calculates elapsed days between two dates. Introduced in Excel 2013, the function offers one new trick: It works with text dates as well as real dates. This function takes the following arguments:

end_date, start_date—The two dates between which you want to know the number of days. If either argument is text, that argument is passed through DATEVALUE() to return a date.

=EOMONTH(start_date,months)

The EOMONTH function returns the serial number for the last day of the month that is the indicated number of months before or after start_date. You use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. This function takes the following arguments:

start_date—This is a date that represents the starting date.

months—This is the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date. If months is not an integer, it is truncated.

Click here to view code image

=EOMONTH(A2,0) converts any date to the end of the month.

These functions are great for calculating shipping days when you ship with FedEx or UPS. They are also great for making sure your result doesn’t fall on a bank holiday. Here’s how you do it:

1. In an out-of-the-way section of a spreadsheet, enter any holidays that will fall during the workweek. This might be federal holidays, floating holidays, company holidays, and so on. The list of holidays can either be entered down a column or across a row. In the top portion of Figure 8.25, the holidays are in E2:E11.

2. Enter a starting date in a cell, such as B1.

3. In another cell, enter the number of workdays that the project is expected to take, such as B2.

4. Enter the ending date formula as =WORKDAY(B1,B2,E2:E7).

The NETWORKDAYS function takes two dates and figures out the number of workdays between them. For example, you might have a project that is due on June 17, 2018. If today is April 14, 2018, NETWORKDAYS can calculate the number of workdays until the project is due.

=WORKDAY(start_date,days,holidays)

=NETWORKDAYS(start_date,end_date,holidays)

The NETWORKDAYS function returns the number of whole workdays between start_date and end_date. Workdays exclude weekends and any dates identified in holidays. You use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term. This function takes the following arguments:

start_date—This is a date that represents the start date.

end_date—This is a date that represents the end date.

holidays—This is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates. If any argument is not a valid date, NETWORKDAYS returns a #NUM! error.

Both of the functions described in this section assume that Saturday and Sunday are weekends and are not workdays. If you have any other weekend system, you can use WORKDAY.INTL or NETWORKDAYS.INTL, as described in the next section.

In Figure 8.25, the current date is entered in cell B6. The project due date is entered in cell B7. The holidays range is in E2:E11, as in the previous example. The formula in cell B8 to calculate workdays is =NETWORKDAYS(B6,B7,E2:E11).

=WORKDAY.INTL(start_date,days,weekend,holidays)

=NETWORKDAYS.INTL(start_date,end_date,weekend,holidays)

Both of these functions work as their noninternational equivalents, with the addition of having the weekend specified as follows:

1—Weekend on Saturday and Sunday

2—Weekend on Sunday and Monday

3—Weekend on Monday and Tuesday

4—Weekend on Tuesday and Wednesday

5—Weekend on Wednesday and Thursday

6—Weekend on Thursday and Friday

7—Weekend on Friday and Saturday

11—Sunday only

12—Monday only

13—Tuesday only

14—Wednesday only

15—Thursday only

16—Friday only

17—Saturday only

You can specify any nonstandard work week by using a seven-digit binary text as the weekend argument. The seven digits correspond to Monday through Sunday in order. A 1 indicates the company is closed that day (that is, it is a weekend), and a 0 indicates the company is open.

For example, the Hartville Marketplace is open Monday, Thursday, Friday, and Saturday. The weekend argument would be “0110001”, as shown in Figure 8.26.

You might sometimes be frustrated because you receive data from other users, and the text is not in the format you need. Or the mainframe might send customer names in uppercase, or the employee in the next department might put a whole address into a single cell. Excel provides text functions to deal with all these situations and more.

Some people prefer to use the CONCATENATE function instead of the &. This function does not perform the way that I want it to perform, and I generally avoid it, but it is described in the following section.

=CONCATENATE(text1,text2,...)

The CONCATENATE function joins several text strings into one text string. The arguments text1, text2,... are 1 to 255 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.

The problem with this function is that it can select only single-cell references. An attempt to use =CONCATENATE(A2:B2) returns a #VALUE! error. If you have to enter =CONCATENATE(A2," ",B2), it is easier to use =A2&" "&B2.

In cell E13, text was entered by someone who never turns off Caps Lock. You can convert this uppercase to lowercase with =LOWER(E13).

In column E, you see a range of names in uppercase. You can use =PROPER(E2) to convert the name to proper case, which capitalizes just the first letter of each word. The PROPER function is mostly fantastic, but there are a few cells that you have to manually correct. PROPER correctly capitalizes names with apostrophes, such as O’Rasi in cell F3. It does not, however, correctly capitalize the interior c in McCartney in cell F4. The function is also notorious for creating company names such as Ibm, 3m, and Aep.

The LOWER function converts all uppercase letters in a text string to lowercase. The argument text is the text you want to convert to lowercase. LOWER does not change characters in text that are not letters.

The PROPER function capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. It converts all other letters to lowercase letters.

The argument text is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.

The UPPER function converts text to uppercase. The argument text is the text you want converted to uppercase. text can be a reference or text string.

You may have trailing spaces at the end of text cells. Although " ABC" and "ABC " might look alike when viewed in Excel, they cause functions such as MATCH and VLOOKUP to fail. TRIM removes leading and trailing spaces.

In Figure 8.29, you can see a simple VLOOKUP in column B. The formula in cell B2 is =VLOOKUP(A2,$F$2:$G$5,2,FALSE). Even though you can clearly see that M40498 is in the lookup table, VLOOKUP returns an #N/A! error, indicating that the product ID is missing from the lookup table.

To diagnose and correct this problem, follow these steps:

1. Select one of the data cells in column F. Press the F2 key to put the cell in Edit mode. A flashing insertion character appears at the end of the cell. Check to see if the flashing cursor is immediately after the last character.

2. Select one of the data cells in column A. Press the F2 key to put the cell in Edit mode. Note whether the flashing insertion character is immediately after the last character. Figure 8.30 shows that the products in column A have several trailing spaces after them. The products in the lookup table do not have any trailing spaces.

3. If the problem is occurring in the values being looked up, you could modify the formula in cell B2 to use the TRIM function. The new formula would be =VLOOKUP(TRIM(A2),$F$2:$G$5,2,FALSE). Figure 8.31 shows how this solves the problem.

4. If the problem is occurring in the first column of the lookup table, insert a new temporary column. Enter the function =TRIM(F2) in the temporary column. Copy this formula down to all rows of the lookup table. Copy the new formulas. Select A2. Select Home, Paste, Values to paste the new values. Although the old and new values look the same, the TRIM function has removed the trailing spaces, and now the products match.

The TRIM function removes all spaces from text except for single spaces between words. You use TRIM on text that you have received from another application that might have irregular spacing. The argument text is the text from which you want spaces removed.

In Figure 8.32, cell C1 contains six letters: ABC DEF. You might assume that the cell is set to be centered. However, the formula in cell C2 appends an asterisk to each end of the value in cell C1. This formula shows that there are several leading and trailing spaces in the value.

Using =LEN(C1) shows that the text actually contains 15 characters instead of six characters. The TRIM(C1) formula removes any leading spaces, any trailing spaces, and any extra interior spaces. The function still leaves one space between ABC and DEF because you want to continue to have words separated by a single space.

The formulas in cells C5 and C6 confirm that the leading and trailing spaces are removed and that the length of the new value is only seven characters.

Today, the Unicode character set includes 110,000 characters, covering most written languages used on Earth. Unicode includes glyphs used in languages from Aboriginal to Yijing. You will find glyphs from Braille, Burmese, Cherokee, Greek, Old Persian, and many languages that you have not heard of. There are also map symbols, playing card symbols, emoticons, dice, domino, and mahjong markings. Unfortunately, the Unicode organization officially rejected including Klingon in 2001. Also, although the Calibri font will render chess, dice, and playing card symbols, it does not support domino or mahjong.

All versions of Excel supported CHAR() to generate symbols 0 through 255. Excel 2013 added support for UNICHAR() to render the 100,000+ symbols defined by Unicode.

You might have ventured into Start, All Programs, Accessories, System Tools, Character Map to find a particular character in the Wingdings character set. Also, if you have a favorite symbol, you might have memorized that you can insert the symbol by using a hotkey. For example, if you hold down Alt, type 0169 on the numeric keypad, and then release Alt, an Office program inserts the copyright symbol (©).

The CHAR function returns the character specified by a number. You use CHAR to translate code page numbers you might get from files on other types of computers into characters.

The argument number is a number between 1 and 255 that specifies which character you want. The character is from the character set used by your computer.

The UNICHAR function returns the Unicode character specified by a number.

Figure 8.33 shows some symbols available from CHAR and UNICHAR.

If you see a strange character in your data, you can learn the character number by using the CODE or UNICODE function, as described in the following section.

The old CHAR function did not work with characters beyond the first 255 characters. Starting in Excel 2013, the Excel team added the UNICODE function to return the Unicode character number for a character.

The CODE function returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. The argument text is the text for which you want the code of the first character. This is an important distinction. CODE returns the code for only the first character in a cell. =CODE("A") and =CODE("ABC") return only 65 to indicate the capital letter A.

The UNICODE function returns the character code for the 100,000+ characters currently defined.

Column A in Figure 8.34 contains part numbers. As you might guess, the Part Number field contains two pieces of information: a three-character vendor code, a dash, and a five-digit part number.

When a customer comes in to buy a part, he probably doesn’t care about the vendor. So the real question is, “Do you have anything in stock that can fix my problem?”

Excel offers three functions—LEFT, MID, and RIGHT—that enable you to isolate just the first or just the last characters, or even just the middle characters, from a column.

The LEFT function returns the first character or characters in a text string, based on the number of characters specified. This function takes the following arguments:

text—This is the text string that contains the characters you want to extract.

num_chars—This specifies the number of characters you want LEFT to extract. num_chars must be greater than or equal to zero. If num_chars is greater than the length of text, LEFT returns all of text. If num_chars is omitted, it is assumed to be 1.

The RIGHT function returns the last character or characters in a text string, based on the number of characters specified. This function takes the following arguments:

text—This is the text string that contains the characters you want to extract.

num_chars—This specifies the number of characters you want RIGHT to extract. num_chars must be greater than or equal to zero. If num_chars is greater than the length of text, RIGHT returns all of text. If num_chars is omitted, it is assumed to be 1.

=MID(text,start_num,num_chars)

MID returns a specific number of characters from a text string, starting at the position specified, based on the number of characters specified. This function takes the following arguments:

text—This is the text string that contains the characters you want to extract.

start_num—This is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on. If start_num is greater than the length of text, MID returns "" (that is, empty text). If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text. If start_num is less than 1, MID returns a #VALUE! error.

num_chars—This specifies the number of characters you want MID to return from text. If num_chars is negative, MID returns a #VALUE! error.

In Figure 8.34, it is easy to extract the three-digit vendor code by using =LEFT(A2,3). It is a bit more difficult to extract the part number. As you scan through the values in column A, it is clear that the vendor code is consistently three letters. With the dash in the fourth character of the text, it means that the part number starts in the fifth position. If you are using MID, you therefore use 5 as the start_num argument.

However, there are a few thousand part numbers in the data set. Right up front, in cell A4, is a part number that breaks the rule. LUK-04-158 contains six characters after the first dash. This might seem to be an isolated incident, but in row 10, BWW-BC42TW also contains six characters after the dash. Because this type of thing happens in real life, two errors in the first nine records are enough to warrant a little extra attention. The four possible strategies for extracting the part number are listed in G2:G6. They are as follows:

Ask MID to start at the fifth character and return a large enough number of characters to handle any possible length (that is, =MID(A2,5,100)).

Ask MID to start at the fifth character but use TRIM around the whole function to prevent any trailing spaces from being included (that is, =TRIM(MID(A2,5,100))).

Ask MID to start at the fifth character, but calculate the exact number of characters by using the LEN function (that is, =MID(A2,5,LEN(A2)-4)).

Skip MID altogether and ask RIGHT to return all the characters after the first dash. This requires you to use the FIND function to locate the first dash—that is, =RIGHT(A2,LEN(A2)-FIND("-",A2)).

The LEN function returns the number of characters in a text string. The argument text is the text whose length you want to find. Spaces count as characters.

There are instances in which you can use LEN along with LEFT, MID, or RIGHT to isolate a portion of text.

You can also use LEN to find records that are longer than a certain limit. Suppose you are about to order nameplates for company employees. Each nameplate can accommodate 15 characters. In Figure 8.35, you add the LEN function next to the names and sort by the length, in descending order. Any problem names appear at the top of the list.

First, let’s look at an example of using FIND to determine whether a word exists in another cell. Figure 8.36 shows a database of customers. The database was created by someone who doesn’t know Excel and jammed every field into a single cell.

Here is how to make this work properly:

1. To find all the customers in California, in cell B2, enter =FIND(“, CA”,A2). When you enter the formula, you get a #VALUE! error. This is okay. In fact, it is useful information: It tells you that CA is not found in the first record.

2. Copy the formula down to all rows.

3. Sort low to high by column B. You’ll see that 98% of the records have a #VALUE! error and sort to the bottom of the list. The few California records have a valid result for the formula in column B and sort to the top of the list, as shown in Figure 8.37.

FIND and SEARCH are similar to one another. The SEARCH function does not distinguish between uppercase and lowercase letters. SEARCH identifies CA, ca, Ca, and cA as matches for CA. If you need to find a cell with exactly AbCdEf, you need to use the FIND command instead of SEARCH. Also, SEARCH allows for wildcard characters in find_text. A question mark (?) finds a single character, and an asterisk (*) finds any number of characters.

The FIND function makes it easy to find the first instance of a particular character in a cell. However, if your text values contain two instances of a character, your task is a bit more difficult. In Figure 8.38, the part numbers in column A really contain three segments, each separated by a dash:

1. To find the first dash, enter =FIND(“-”,A2) in column B.

2. To find the second dash, use the optional start_num parameter to the FIND function. The start_num parameter is a character position. You want the function to start looking after the first instance of a dash. This can be calculated as the result of the first FIND in column B plus one. Thus, the formula in cell C2 is =FIND("-",A2,B2+1).

3. After you find the character positions of the dashes, isolate the various portions of the part number. In column D, for the first part of the number, enter =LEFT(A2,B2–1). This basically asks for the left characters from the part number, stopping at one fewer than the first dash.

4. In column E, for the middle part of the number, enter =MID(A2,B2+1,C2–B2–1). This asks Excel to start at the character position one after the first dash and then continue for a length that is one fewer than the first dash subtracted from the second dash.

5. In column F, for the final part of the number, enter =RIGHT(A2,LEN(A2)–C2). This calculates the total length of the part number, subtracts the position of the second dash, and returns those right characters.

=FIND(find_text,within_text,start_num)

FIND finds one text string (find_text) within another text string (within_text) and returns the number of the starting position of find_text from the first character of within_text. You can also use SEARCH to find one text string within another, but unlike SEARCH, FIND is case sensitive and doesn’t allow wildcard characters.

The FIND function takes the following arguments:

find_text—This is the text you want to find. If find_text is "" (that is, empty text), FIND matches the first character in the search string (that is, the character numbered start_num or 1). find_text cannot contain wildcard characters.

within_text—This is the text that contains the text you want to find.

start_num—This specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

=SEARCH(find_text,within_text,start_num)

SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num. You use SEARCH to determine the location of a character or text string within another text string so that you can use the MID or REPLACE function to change the text.

The SEARCH function takes the following arguments:

find_text—This is the text you want to find. You can use the wildcard characters question mark (?) and asterisk (*) in find_text. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, you type a tilde (~) before the character. If you want to find a tilde, you type two tildes. If find_text is not found, a #VALUE! error is returned.

within_text—This is the text in which you want to search for find_text.

start_num—This is the character number in within_text at which you want to start searching. If start_num is omitted, it is assumed to be 1. If start_num is not greater than zero or is greater than the length of within_text, a #VALUE! error is returned.

=SUBSTITUTE(text,old_text,new_text,instance_num)

The SUBSTITUTE function substitutes new_text for old_text in a text string. You use SUBSTITUTE when you want to replace specific text in a text string; you use REPLACE when you want to replace any text that occurs in a specific location in a text string.

The SUBSTITUTE function takes the following arguments:

text—This is the text or the reference to a cell that contains text for which you want to substitute characters.

old_text—This is the text you want to replace.

new_text—This is the text you want to replace old_text with.

instance_num—This specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

The REPT function repeats text a given number of times. You use REPT to fill a cell with a number of instances of a text string. This function takes the following arguments:

text—This is the text you want to repeat.

number_times—This is a positive number that specifies the number of times to repeat text. If number_times is 0, REPT returns "" (that is, empty text). If number_times is not an integer, it is truncated. The result of the REPT function cannot be longer than 32,767 characters.

In Microsoft Word, it is easy to create a row of periods between text and a page number. In Excel, you have to resort to clever use of the REPT function to do this.

In Figure 8.39, column A contains a page number. Column B contains a chapter title. The goal in column C is to join enough periods between columns B and A to make all the page numbers line up.

The number of periods to print is the total desired length, less the length of columns A and B. The formula for cell C2 is =B2&REPT(".",45-(LEN(A2)+LEN(B2)))&A2.

The formula in cell C1 tests whether these values are equal. In the rules of Excel, AbC and ABC are equivalent. The formula in cell C1 indicates that the values are equal. To some people, these two text cells might not be equivalent. If you work in a store that sells the big plastic letters that go on theater marquees, your order for 20 letter a figures should not be filled with 20 letter A figures.

Excel forces you to use the EXACT function to compare these two cells to learn that they are not the same.

The EXACT function compares two text strings and returns TRUE if they are the same and FALSE otherwise. EXACT is case sensitive but ignores formatting differences. You use EXACT to test text being entered into a document. This function takes the following arguments:

text1—This is the first text string.

text2—This is the second text string.

Consider Figure 8.41. Cell A11 contains a date and is formatted as a date. When you join the name in cell B11 with the date in cell A11, Excel automatically converts the date back to a numeric serial number. This is frustrating.

Today, the TEXT function is the most versatile solution to this problem. If you understand the basics of custom numeric formatting codes, you can easily use TEXT to format a date or a number in any conceivable format. For example, the formula in cell C12 uses =TEXT(A12,"m/d/y") to force the date to display as a date.

The TEXT function gives you a lot of versatility. To learn the custom formatting codes for a cell, you can select the cell, display the Format Cells dialog (by pressing Ctrl+1), and select the Custom category on the Number tab. Excel shows you the codes used to create that format.

If you don’t care to learn the number formatting codes, you can use either the DOLLAR or FIXED function to return a number as text, with a few choices regarding number of decimals and whether Excel should use the thousands separator. The formulas shown in C1:C7 in Figure 8.41 return the formatted text values shown in column B.

The TEXT function converts a value to text in a specific number format. Formatting a cell with an option on the Number tab of the Format Cells dialog changes only the format, not the value. Using the TEXT function converts a value to formatted text, and the result is no longer calculated as a number.

The TEXT function takes the following arguments:

value—This is a numeric value, a formula that evaluates to a numeric value, or a reference to a cell that contains a numeric value.

format_text—This is a number format in text form from the Category box on the Number tab in the Format Cells dialog. format_text cannot contain an asterisk (*) and cannot be the general number format.

=T("text") returns the original text. If cell B1 contains the number 123, =T(B1) would return empty text. Basically, T() returns the value in the cell only if it is text.

=VALUE() converts text that looks like a number or a date to the number or the date.