Calculating the number of years between two dates

Calculating the number of years between two dates

The following formula calculates the number of years between two dates. This formula assumes
that cells A1 and B1 both contain dates:

  =YEAR(A1)-YEAR(B1)

This formula uses the YEAR function to extract the year from each date and then subtracts one
year from the other. If cell B1 contains a more recent date than the date in cell A1, then the result
is negative.

Displaying any date

Displaying any date

As explained earlier in this chapter, you can easily enter a date into a cell by simply typing it,
using any of the date formats that Excel recognizes. You can also create a date by using the
DATE function, which takes three arguments: the year, the month, and the day. The following for-
mula, for example, returns a date comprising the year in cell A1, the month in cell B1, and the day
in cell C1:

  =DATE(A1,B1,C1)

              The DATE function accepts invalid arguments and adjusts the result accordingly. For
              example, this next formula uses 13 as the month argument, and returns January 1, 2010.
              The month argument is automatically translated as month 1 of the following year.

                  =DATE(2009,13,1)

Often, you’ll use the DATE function with other functions as arguments. For example, the formula
that follows uses the YEAR and TODAY functions to return the date for Independence Day (July
4th) of the current year:

  =DATE(YEAR(TODAY()),7,4)

The DATEVALUE function converts a text string that looks like a date into a date serial number.
The following formula returns 40412, the date serial number for August 22, 2010:

  =DATEVALUE(“8/22/2010”)

Date-Related Functions

Date-Related Functions

Excel has quite a few functions that work with dates. They are all listed under the Date & Time
drop-down list in the Formulas➜Function Library group.

Table 6-4 summarizes the date-related functions available in Excel.

Table 6-4: Date-Related Functions

 Function                      Description

 DATE                          Returns the serial number of a date given the year, month, and day

 DATEDIF                       Calculates the number of days, months, or years between two dates

 DATEVALUE                     Converts a date in the form of text to an actual date

 DAY                           Returns the day of the month for a given date

 DAYS360                       Calculates the number of days between two dates based on a 360-day year

 EDATE*                        Returns the date that represents the indicated number of months before or
                              after the start date

 EOMONTH*                      Returns the date of the last day of the month before or after a specified num-
                               ber of months

 MONTH                         Returns the month for a given date

 NETWORKDAYS*                  Returns the number of whole work days between two dates

 NETWORKDAYS.INTL**            An international version of the NETWORKDAYS function

 NOW                           Returns the current date and time

 TODAY                         Returns today’s date

 WEEKDAY                       Returns the day of the week (expressed as a number) for a date

 WEEKNUM*                      Returns the week number of the year for a date

 WORKDAY*                      Returns the date before or after a specified number of workdays

 WORKDAY.INTL**               An international version of the WORKDAY function

 YEAR                          Returns the year for a given date

 YEARFRAC*                     Returns the year fraction representing the number of whole days between two
                               dates

* In versions prior to Excel 2007, this function is available only when the Analysis ToolPak add-in is installed.
** Indicates a function that’s new to Excel 2010.

Displaying the current date

The following function displays the current date in a cell:

  =TODAY()
 

Problems with dates

Problems with dates

Excel has some problems when it comes to dates. Many of these problems stem from the fact
that Excel was designed many years ago, before the acronym Y2K became a household term.
And, as I describe, the Excel designers basically emulated the Lotus 1-2-3 limited date and time
features, which contain a nasty bug duplicated intentionally in Excel. In addition, versions of Excel
show inconsistency in how they interpret a cell entry that has a two-digit year. And finally, how
Excel interprets a date entry depends on your regional date settings.

If Excel were being designed from scratch today, I’m sure it would be much more versatile in
dealing with dates. Unfortunately, we’re currently stuck with a product that leaves much to be
desired in the area of dates.

The Excel leap year bug

A leap year, which occurs every four years, contains an additional day (February 29). Specifically,
years that are evenly divisible by 100 are not leap years, unless they are also evenly divisible by
400. Although the year 1900 was not a leap year, Excel treats it as such. In other words, when
you type the following into a cell, Excel does not complain. It interprets this as a valid date and
assigns a serial number of 60:

  2/29/1900

If you type the following invalid date, Excel correctly interprets it as a mistake and doesn’t con-
vert it to a date. Rather, it simply makes the cell entry a text string:

  2/29/1901

How can a product used daily by millions of people contain such an obvious bug? The answer is
historical. The original version of Lotus 1-2-3 contained a bug that caused it to consider 1900 as a
leap year. When Excel was released some time later, the designers knew of this bug and chose to
reproduce it in Excel to maintain compatibility with Lotus worksheet files.

Formatting dates and times

Formatting dates and times

You have a great deal of flexibility in formatting cells that contain dates and times. For example,
you can format the cell to display the date part only, the time part only, or both the date and
time parts.

You format dates and times by selecting the cells and then using the Number Format control in
the Home➜Number group (see Figure 6-1). This control offers two date formats and one time
format.

Figure 6-1: Use the Number Format drop-down list to change the appearance of dates and times.

             When you create a formula that refers to a cell containing a date or a time, Excel may
             automatically format the formula cell as a date or a time. Sometimes, this is very help-
             ful; other times, it’s completely inappropriate and downright annoying. Unfortunately,
             you cannot turn off this automatic date formatting. You can, however, use a shortcut
             key combination to remove all number formatting from the cell and return to the
             default General format. Just select the cell and press Ctrl+Shift+~.

If none of the built-in formats meet your needs, you can create a custom number format. Select
the More Number Formats option from the Number Format drop-down list to display the Number
tab in the Format Cells dialog box. The Date and Time categories provide many additional for-
matting choices. If none of these are satisfactory, select the Custom category and type the cus-
tom format codes into the Type box. (See Appendix B for information on creating custom
number formats.)

Counting the number of words in a cell

Counting the number of words in a cell

The following formula returns the number of words in cell A1:

  =LEN(TRIM(A1))-LEN(SUBSTITUTE((A1),” “,””))+1

The formula uses the TRIM function to remove excess spaces. It then uses the SUBSTITUTE func-
tion to create a new string (in memory) that has all the space characters removed. The length of
this string is subtracted from the length of the original (trimmed) string to get the number of
spaces. This value is then incremented by 1 to get the number of words.

Note that this formula returns 1 if the cell is empty. The following modification solves that problem:

  =IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,””))+1)

             Excel has many functions that work with text, but you’re likely to run into a situation in
             which the appropriate function just doesn’t exist. In such a case, you can often create
             your own worksheet function using VBA. Chapter 25 also contains a number of custom
             text functions written in VBA.

Removing titles from names

You can use the formula that follows to remove four common titles (Mr., Dr., Ms., and Mrs.) from
a name. For example, if cell A1 contains Mr. Fred Munster, the formula would return Fred Munster.

  =IF(OR(LEFT(A1,2)={“Mr”,”Dr”,”Ms”}),RIGHT(A1,LEN(A1)-(FIND(“.”,A1)+1)),A1)

Splitting text string without using formula in excel

Extracting first names, middle names, and last names

Extracting first names, middle names, and last names


Suppose you have a list consisting of people’s names in a single column. You have to separate
these names into three columns: one for the first name, one for the middle name or initial, and
one for the last name. This task is more complicated than you may initially think because not
every name in the column has a middle name or middle initial. However, you can still do it.




The formulas that follow all assume that the name appears in cell A1.
You can easily construct a formula to return the first name:
=IFERROR(LEFT(A1,FIND(“ “,A1)-1),A1)
Returning the middle name or initial is much more complicated because not all names have a
middle initial. This formula returns the middle name or initial (if it exists); otherwise, it returns
nothing:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))>1,MID(A1,FIND(“ “,A1)+1,FIND(“ “,A1,FIND(“ “,
A1)+1)-(FIND(“ “,A1)+1)),””)
Finally, this formula returns the last name:
=IFERROR(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE
(A1,” “,””))))),””)

Extracting all but the first word of a string

Extracting all but the first word of a string

The following formula returns the contents of cell A1, except for the first word:
=RIGHT(A1,LEN(A1)-FIND(“ “,A1,1))
If cell A1 contains 2010 Operating Budget, the formula then returns Operating Budget.
This formula returns an error if the cell contains only one word. The formula below solves this
problem and returns an empty string if the cell does not contain multiple words:
=IFERROR(RIGHT(A1,LEN(A1)-FIND(“ “,A1,1)),””)
For compatibility with versions prior to Excel 2007, use this formula:
=IF(ISERR(FIND(“ “,A1)),””,RIGHT(A1,LEN(A1)-FIND(“ “,A1,1)))

Extracting the last word of a string

Extracting the last word of a string is more complicated because the FIND function only works
from left to right. Therefore, the problem rests with locating the last space character. The formula
that follows, however, solves this problem. It returns the last word of a string (all the text follow-
ing the last space character):
=RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)))))
This formula, however, has the same problem as the first formula in the preceding section: It fails
if the string does not contain at least one space character. The following modified formula uses
the IFERROR function to avoid the error value:
=IFERROR(RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE
(A1,” “,””))))),A1)
For compatibility with versions prior to Excel 2007, use this formula:
=IF(ISERR(FIND(“ “,A1)),A1,RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-
LEN(SUBSTITUTE(A1,” “,””))))))

Extracting the first word of a string

To extract the first word of a string, a formula must locate the position of the first space charac-
ter and then use this information as an argument for the LEFT function. The following formula
does just that:
=LEFT(A1,FIND(“ “,A1)-1)
This formula returns all of the text prior to the first space in cell A1. However, the formula has a
slight problem: It returns an error if cell A1 consists of a single word. A simple modification solves
the problem by using an IFERROR function to check for the error:
=IFERROR(LEFT(A1,FIND(“ “,A1)-1),A1)
For compatibility with versions prior to Excel 2007, use this formula:
=IF(ISERR(FIND(“ “,A1)),A1,LEFT(A1,FIND(“ “,A1)-1))

Extracting a filename from a path specification

Extracting a filename from a path specification

The following formula returns the filename from a full path specification. For example, if cell A1
contains c:\files\excel\myfile.xlsx, the formula returns myfile.xlsx.
=MID(A1,FIND(“*”,SUBSTITUTE(A1,”\”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))+1,LEN(A1))
This formula assumes that the system path separator is a backslash (\). It essentially returns all
the text following the last backslash character. If cell A1 doesn’t contain a backslash character,
the formula returns an error.

Determining a column letter for a column number

Determining a column letter for a column number

This next formula returns a worksheet column letter (ranging from A to XFD) for the value con-
tained in cell A1. For example, if A1 contains 29, the formula returns AC.
=LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)
Note that the formula doesn’t check for a valid column number. In other words, if A1 contains a
value less than 1 or greater than 16,384, the formula then returns an error. The following modifica-
tion uses the IFERROR function to display text (Invalid Column) instead of an error value:
=IFERROR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1),”Invalid Column”)
The IFERROR function was introduced in Excel 2007. For compatibility with versions prior to
Excel 2007, use this formula:
=IF(ISERR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)),
“Invalid Column”,LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1))

Removing trailing minus signs

Some accounting systems use a trailing minus sign to indicate negative values. If you import such
a report into Excel, the values with trailing minus signs are interpreted as text.
The formula that follows checks for a trailing minus sign. If found, it removes the minus sign and
returns a negative number. If cell A1 contains 198.43–, the formula returns –198.43.
=IF(RIGHT(A1,1)=”–”,LEFT(A1,LEN(A1)–1)*–1,A1)

Counting the occurrences of a substring in a cell

The formulas in the preceding section count the number of occurrences of a particular character
in a string. The following formula works with more than one character. It returns the number of
occurrences of a particular substring (contained in cell B1) within a string (contained in cell A1).
The substring can consist of any number of characters.
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,””)))/LEN(B1)
For example, if cell A1 contains the text Blonde On Blonde and B1 contains the text Blonde, the
formula returns 2.
The comparison is case sensitive, so if B1 contains the text blonde, the formula returns 0. The fol-
lowing formula is a modified version that performs a case-insensitive comparison:
=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),””)))/LEN(B1)

Counting specific characters in a cell

This formula counts the number of Bs (uppercase only) in the string in cell A1:
=LEN(A1)-LEN(SUBSTITUTE(A1,”B”,””))
This formula uses the SUBSTITUTE function to create a new string (in memory) that has all the Bs
removed. Then the length of this string is subtracted from the length of the original string. The
result reveals the number of Bs in the original string.
The following formula is a bit more versatile. It counts the number of Bs (both upper- and lower-
case) in the string in cell A1.
=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,”B”,””),”b”,””))

Searching and replacing within a string excel

You can use the REPLACE function in conjunction with the SEARCH function to create a new
string that replaces part of the original text string with another string. In effect, you use the
SEARCH function to find the starting location used by the REPLACE function.
For example, assume cell A1 contains the text Annual Profit Figures. The following formula
searches for the word Profit and replaces those six characters with the word Loss:

=REPLACE(A1,SEARCH(“Profit”,A1),6,”Loss”)

This next formula uses the SUBSTITUTE function to accomplish the same effect in a more effi-
cient manner:

=SUBSTITUTE(A1,”Profit”,”Loss”)

Finding searching with in string in excel

Replacing text in excel