USE THE LEFT, MID AND RIGHT FUNCTIONS TO EXTRACT DATE PARTS USING MICROSOFT EXCEL

The Microsoft Excel worksheet in Figure 4-8 contains date values in column A. Excel cannot interpret these values as dates. To show the date in a correct format, the values of column A have to be extracted to year, month, and day. 

To extract, combine, and display the correct format:

1. Select cells B2:B10 and type the following formula: =DATE(LEFT(A2,4),MID(A2,FIND(".",A2,1)+1,2), RIGHT(A2,2)).
2. Press <-Ctrl+Enter->.

USE THE LEFT, MID AND RIGHT FUNCTIONS TO EXTRACT DATE PARTS USING MICROSOFT EXCEL

Note: The first four digits have to be transferred with the LEFT function. Then use the FIND function to detect the decimal point. On the right of the first decimal point (+1), two digits are interpreted as the month using the MID function. On the right side of the second decimal point, use the RIGHT function to extract two digits as the day value.

USE THE DATE FUNCTION TO COMBINE COLUMNS WITH DATE PARTS USING MICROSOFT EXCEL

The Excel worksheet shown in Figure 4-7 uses three columns showing dates. Column A lists years, column B lists months using numbers from 1 to 12, and column C contains the days of a month from 1 to 31. These columns have to be combined to show one formatted date. 

To do so, use the DATE function.

DATE(year, month, day)

year: This argument can be from one to four digits. Microsoft Excel for Windows uses the 1900 date system. 

month: A number representing the month of the year (1 to 12).
day: A number representing the day of the month (1 to 31).

To combine values of cells into one date:

1. Select cells D2:D10 and type the following formula:=DATE(A2,B2,C2).
2. Press <-Ctrl+Enter->.

Note that Excel knows which years are leap years, and thus provides correct results even when incorrect data is entered, as in row 3.

USE THE DATE FUNCTION TO COMBINE COLUMNS WITH DATE PARTS USING MICROSOFT EXCEL

USE THE NOW FUNCTION TO CALCULATE THE TIME USING MICROSOFT EXCEL

To calculate with time using Now() Function in Microsoft Excel, it is helpful to know that Excel stores the time as a decimal value. For example, 0.5 is 12:00 noon, 0.75 is 18:00, and so on. 

To calculate with time:

1. In cell B1 type the formula =NOW().
2. In cell B2 type the formula =B1+0.25 to add six hours to the current time in cell B1.

3. Type the following formula in cell C1: ="The meeting starts at " & TEXT(B1,"hh:mm") & " and ends at " & TEXT(B2,"hh:mm").

4. Press <-Enter->.

USE THE NOW FUNCTION TO CALCULATE THE TIME USING MICROSOFT EXCEL

USE THE NOW FUNCTION TO SHOW THE CURRENT TIME USING MICROSOFT EXCEL

The previous tip described how to get the current date. Now we want to determine the current time in microsoft excel. The NOW function in Microsoft Excel returns the serial number of the current date and time. Microsoft Excel stores dates as sequential numbers so they can be used in calculations. By default, January 1, 1900, is number 1, and January 1, 2006, is number 38718 because it is 38,717 days after January 1, 1900. Numbers to the right of the decimal point in the number represent the time; numbers to the left represent the date. For example, the serial number .5 represents the time 12:00 noon. The NOW() function is not updated continuously.

To show the current time:

1. In cell A1 type the formula =NOW() and press <-Enter->.
2. Ensure that cell A1 is selected and choose Cells from the Format menu.
3. In the Number tab, select Date under Category.
4. Select the format 3/14/01 1:30 PM.
5. Press <-Enter->

USE THE NOW FUNCTION TO SHOW THE CURRENT TIME USING MICROSOFT EXCEL

USE THE TEXT FUNCTION TO CALCULATE WITH THE TODAY FUNCTION USING MICROSOFT EXCEL

How to use the Text Function to calculate with the Today Function in Microsoft Excel? A project starts today and ends 10 days later. These dates are shown in cells B1 and B2. The end date has to be calculated based on the start date, and the dates have to be combined with additional text to form the message shown in cell A4. It can be used for project duration calculation also.

To calculate with the TODAY function:

1. In cell B1 type the formula =TODAY().
2. In cell B2 type the formula =TODAY()+10 to add ten days to the current date.

3. Select cell A4 and type the following formula: ="The project starts on " & TEXT(B1,"MM/DD/YYYY") & " and ends on " & TEXT(B2,"MM/DD/YYYY").

4. Press <-Enter->.

USE THE TEXT FUNCTION TO CALCULATE WITH THE TODAY FUNCTION USING MICROSOFT EXCEL

Note: The TEXT function (TEXT(value, format_text)) converts a value to text in a specific number format. In this example, format_text is shown as MM = month (two digits), DD = day(two digits), and YYYY = year (four digits).

USE THE TODAY FUNCTION TO CHECK FOR FUTURE DATES USING MICROSOFT EXCEL

How to Check for Future Dates? In a worksheet, dates in column A have to be checked to see if they are in the future. The actual date can be determined by using the TODAY function in Microsoft Excel and can be compared with the dates in the worksheet using the IF function of Microsoft Excel. If dates are in the future, the result in column B should be Y; otherwise, it should be N. 

To check for future dates:

1. In cell D1, type the formula =TODAY() to show the current date.
2. Select cells B2:B10 and type the following formula: =IF(A2<=TODAY(),"n","y").
3. Press <-Ctrl+Enter->.

USE THE TODAY FUNCTION TO CHECK FOR FUTURE DATES USING MICROSOFT EXCEL

USE THE WEEKDAY FUNCTION TO DETERMINE THE WEEKEND USING MICROSOFT EXCEL

How do you find out whether or not a date falls on a weekend? To answer this question you can either use the previous tip or use the more convenient WEEKDAY function in Microsoft Excel. This function returns the day of the week as a number corresponding to a date. The returned number is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. 

To determine the weekend:

1. Select cells B2:B10 and type the formula =A2.
2. Press <-Ctrl+Enter->.
3. From the Format menu, select Cells.
4. Select the Number tab and click on Custom below Category.
5. In the Type box, change the number format to "dddd".
6. Press OK.
7. Select cells C2:C10 and type the following formula: =IF(OR(WEEKDAY(A2)=7,WEEKDAY(A2)=1),"weekend","").
8. Press <-Ctrl+Enter->.

USE THE WEEKDAY FUNCTION TO DETERMINE THE WEEKEND USING MICROSOFT EXCEL

Note: In column B use the custom format dddd to check the result of column C.

USE THE TEXT FUNCTION TO COMBINE AND FORMAT TEXT USING MICROSOFT EXCEL

In a daily sales record, employee names are listed in column A and their daily sales are entered in column B. There are two tasks here: We need to determine the percentage of the weekly sales goal ($1,000) that was met by the daily sales, and we want to combine the information from columns A and B.

USE THE TEXT FUNCTION TO COMBINE AND FORMAT TEXT USING MICROSOFT EXCEL

To combine and format text:

1. In a worksheet, copy the data shown in Figure 3-30.
2. Select cells C2:C10 and type the formula =B2/1000.
3. Press <-Ctrl+Enter->.

4. Select cells D2:D10 and type the following formula: =A2 & " sold " & TEXT(B2,"$0.00") & "today. That's " & TEXT(C2,"0.0%") & " of weekly goal.

5. Press <-Ctrl+Enter->.

USE THE TEXT FUNCTION TO COMBINE AND FORMAT TEXT USING MICROSOFT EXCEL

USE THE T FUNCTION TO CHECK FOR VALID NUMBERS USING MICROSOFT EXCEL

How to check whether a cell contains a number or text in Microsoft Excel? Take a look at Figure 3-29. Notice that some numbers are listed, but there are also references to text and other values. You can check whether a number is a real value in an Excel worksheet by using the T() function in Microsoft Excel. This function checks whether a value is text. If it is text, T returns the text; if it is not, T returns empty text. 

To check for valid numbers:

1. Enter some values in column A and change the format for some of them to text (using the Cells option from the Format menu).

2. Select cells B2:B10 and type the following formula: =T(A2).
3. Press <-Ctrl+Enter->.

USE THE T FUNCTION TO CHECK FOR VALID NUMBERS USING MICROSOFT EXCEL

USE THE DOLLAR FUNCTION TO CONVERT NUMBERS TO CURRENCY IN TEXT FORMAT USING MICROSOFT EXCEL

The DOLLAR function of Microsoft Excel converts a number to text format and applies a currency symbol. The currency format will be rounded to the specified decimal place.

 DOLLAR(number, decimals)

number: A number, a reference to a cell that contains a number, or a formula that calculates a value. decimals: The number of digits to the right of the decimal point.

If negative, number is rounded to the left of the decimal point.

If omitted, Excel sets it to 2.

To convert numbers to currency:
1. In cells A2:A10, type numeric values.
2. Select cells B2:B10 and type the following formula: =DOLLAR(A2,2).
3. Press <-Ctrl+Enter->.

USE THE DOLLAR FUNCTION TO CONVERT NUMBERS TO CURRENCY IN TEXT FORMAT USING MICROSOFT EXCEL

USE THE CODE FUNCTION TO DETERMINE THE NUMERIC CODE OF A CHARACTER USING MICROSOFT EXCEL

To return the numeric, or ASCII, code for the first character in a text string, use the CODE function in Microsoft Excel. This function returns the code corresponding to the currently used character set. 

To determine the numeric code of a character:

1. In cells A2:A10, type letters of the alphabet, both uppercase and lowercase.
2. Select cells B2:B10 and type the following formula: =CODE(A2).
3. Press <-Ctrl+Enter->.

USE THE CODE FUNCTION TO DETERMINE THE NUMERIC CODE OF A CHARACTER USING MICROSOFT EXCEL

USE THE CHAR FUNCTION TO DETERMINE SPECIAL CHARACTERS USING MICROSOFT EXCEL

To use special characters, it is necessary to figure out how to get them. The CHAR function in Microsoft Excel will return the character specified by a number in column A. Note that some fonts may have different special characters.  

To determine special characters:

1. Copy Column A as shown below to your worksheet.
2. Select cells B2:B16 and type the following formula:=CHAR(A2).
3. Press <-Ctrl+Enter->.

USE THE CHAR FUNCTION TO DETERMINE SPECIAL CHARACTERS IN MICROSOFT EXCEL

USE THE CHAR FUNCTION TO CHECK YOUR FONTS USING MICROSOFT EXCEL

To check a few fonts at the same time, open a new worksheet and format columns B to E with the Arial, Wingdings, Webdings, and Terminal fonts. Use the CHAR function of Microsoft Excel to return the character specified by a number in Column A.  

To check installed fonts:

1. In cell A2, type 1.
2. Press <-Ctrl-> and drag the right corner of cell A2 down to cell A256.
3. Select cells B2:E256 and type the following formula: =CHAR($A2).
4. Press <-Ctrl+Enter->.

USE THE CHAR FUNCTION TO CHECK YOUR FONTS USING MICROSOFT EXCEL

USE THE REPT FUNCTION TO SHOW DATA IN A CHART USING MICROSOFT EXCEL

To show data in a chart-like view (Showing Percentage of Progress in graphical form in Excel Cell), you can define a character and repeat this character a specified number of times using the REPT text function in Microsoft Excel. 

To show data in a chart:

1. In cells B2:B10, type percentages in the range of 1% to 100%.
2. Select cells C2:C10 and type the following formula: =REPT("|",B2*100).
3. Press <-Ctrl+Enter->.

USE THE REPT FUNCTION TO SHOW DATA IN A CHART USING MICROSOFT EXCEL

USE THE REPT FUNCTION TO SHOW DATA IN GRAPHICS MODE USING MICROSOFT EXCEL

To demonstrate data in a chart-like view, you can use a special character in a symbol font and repeat the character. To do so, use the REPT text function of Microsoft Excel. This function repeats a character a given number of times. 

To show data in a simple chart:

1. In cells A2:A10, type numbers from 1 to 10.
2. Select cells B2:B10 and type the following formula: =REPT("n",A2).
3. Press <-Ctrl+Enter->. 4. From the Format menu, select Cells.
5. Select the Font tab.
6. Select Wingdings from the Font list and click OK.

USE THE REPT FUNCTION TO SHOW DATA IN GRAPHICS MODE USING MICROSOFT EXCEL

USE THE CLEAN TEXT FUNCTION TO REMOVE ALL NONPRINTABLE CHARACTERS USING MICROSOFT EXCEL

If data is imported from other applications, it is possible for this data to contain characters that may not be printable. In this case, the CLEAN text function of Microsoft Excel can be used to remove all nonprintable characters from text. 

To delete nonprintable characters:

1. Type any text in cells A2:A5. Make sure that some of the cells contain nonprintable characters.
2. Select cells A2:A5 and type the following formula: =CLEAN(A2).
3. Press <-Ctrl+Enter->.

USE THE CLEAN TEXT FUNCTION TO REMOVE ALL NONPRINTABLE CHARACTERS USING MICROSOFT EXCEL

USE THE TRIM FUNCTION TO CONVERT "TEXT-NUMBERS" TO REAL NUMBERS USING MICROSOFT EXCEL

In this example numbers entered as text have to be converted to values. To do this, use the VALUE and TRIM Text functions in combination to get the correct result. The VALUE function converts a text string that represents a number to a number, and the TRIM function deletes all leading and trailing spaces.  

To convert text that represents a number to a value:

1. Format column A as text.
2. In cells A2:A10, type a series of numbers with leading spaces.
3. Select cells B2:B10 and type the following formula: =VALUE(TRIM(A2)).
4. Press <-Ctrl+Enter->.

USE THE TRIM FUNCTION TO CONVERT "TEXT-NUMBERS" TO REAL NUMBERS USING MICROSOFT EXCEL

USE THE TRIM FUNCTION TO DELETE SPACES IN MICROSOFT EXCEL

Column A of a worksheet contains text with spaces at the left and right side of the text. This could be a problem if, for example, data is used for evaluation.  Use the TRIM Text function to remove all spaces from a text string except for the single spaces between words. 

 To delete unneeded spaces from text:

1. In cells A2:A5, type text with leading and trailing spaces.
2. Select cells B2:B5 and type the following formula: =TRIM(A2).
3. Press <-Ctrl+Enter->.

USE THE TRIM FUNCTION TO DELETE SPACES IN MICROSOFT EXCEL

USE THE FIXED FUNCTION TO ROUND AND CONVERT NUMBERS TO TEXT USING MICROSOFT EXCEL

To round numbers and return the result as text, use the FIXED function. This function rounds a number to the specified number of decimals, returning the result as text with or without commas.

FIXED(number, decimals, no_commas)

number: The number to round and convert to text.
decimals: The number of digits to the right of the decimal point. If omitted, Excel sets it to 2.

no_commas: A logical value that prevents FIXED from including commas when set to TRUE. If no_commas is FALSE or omitted, the returned text includes commas.

To round and convert numbers to text:

1. In cells A2:A10, type values with decimals.
2. Select cells B2:B10 and type the following formula: =FIXED(A2,-1,FALSE).
3. Press <-Ctrl+Enter->.
4. Select cells C2:C10 and type the following formula: =FIXED(A2,-2,FALSE).
5. Press <-Ctrl+Enter->.

USE THE FIXED FUNCTION TO ROUND AND CONVERT NUMBERS TO TEXT USING MICROSOFT EXCEL

USE THE PROPER FUNCTION TO CONVERT INITIAL CHARACTERS FROM LOWERCASE TO UPPERCASE USING MICROSOFT EXCEL

To convert the first letter in each word to uppercase and all other letters to lowercase, the PROPER function is used. This function capitalizes the first letter in a text string and any letters that follow characters other than a letter (such as a space). All other letters will be changed to lowercase.

This function has the following syntax:

PROPER(text)

text: Text enclosed in quotation marks, a formula that returns text, or a reference to a cell that contains the text that should have an initial capital letter.

To convert a text string to proper case:

1. In cells A2:A7 type any kind of text.
2. Select cells B2:B7 and type the following formula: =PROPER(A2).
3. Press <-Ctrl+Enter->.

USE THE PROPER FUNCTION TO CONVERT INITIAL CHARACTERS FROM LOWERCASE TO UPPERCASE USING MICROSOFT EXCEL