Microsoft® Excel 2013
Lesson 6, pp. 228—258
Using Dates, Times, and Logical Functions
Microsoft® Excel 2013
Lesson 6, pp. 228—258
Using Dates, Times, and Logical Functions
Objectives
3
• Key and format dates and times.1
• Use Date & Time functions.2
• Use date and time arithmetic.3
• Use the IF function.4
• Create and edit cell styles.5• Use the AND, OR, and NOT
functions.6
• Prepare worksheets for output.7
Objective 1
Keying and Formatting Dates and Times
4
Keying and Formatting Dates
When you key a date, Excel usually assigns its default date format. Depending on what you key, however, it may assign a format that more closely resembles what you keyed.Keyed Date Displays
As15-jan-12 15-Jan-12
january 15, 2012
15-Jan-12
2012/01/15 01/15/12You can REFORMAT a date to a preset or a custom
format as needed
Creating a Custom Date Format
A custom date format is built using Excel format codes
The sections have codes for how to display positive values, negative values, zero and text
A custom date format has one section
You can use a preset format and edit it to be your own
Note: See Table 6-1, p. 230 for Date Format Codes
Keying and Formatting Times
When you key a time, you should key “am,” “pm,” or a colon to indicate that the value is a timeKeyed
Time Displays As
9 am 9:00 AM
10:3010:30
(is assumed as AM)
3:30 pm 3:30 PM
13:3013:30
(is assumed as PM)
You can REFORMAT a time to a preset or custom format and specify a 12-hour or 24-hour clock
Creating a Custom Time Format
A custom time format is built using Excel format codes
h m s AM/PM
Objective 2
Using Date and Time Functions
9
Using Date and Time Functions Excel’s date and time values use a
serial number system A serial number is a unique value
assigned to every date Serial numbers allow Excel to perform
date and time arithmetic Excel’s serial number system starts with:
January 1, 1900, is counted as 1 January 2, 1900, is counted as 2 Numbers are assigned values up to and
including December 31, 9999
Using TODAY() and NOW() Functions
TODAY() – Displays the current date NOW() – Displays the current date
and time Both functions have NO arguments
and use the computer’s clock to display the current date
Either result can be formatted to show the date and/or the time
Neither of these functions has arguments.
Both of these functions are volatile (unstable)
Objective 3
Using Date and Time Arithmetic
12
Using Date Arithmetic
To determine an age, subtract the start or birth date from TODAY() or other relevant date
Divide those results by 365.25 Use parenthesis to subtract
first Example:
=(Today( ) –
Birthdate)/365.25
Using Time Arithmetic
To determine time passed, subtract the start time from the end time
Multiply by 24 to convert the results to hours
Example:
=(End Time – Start
Time)*24
Objective 4
Using the IF Function
15
Using the IF Function
A logical function is an Excel formula that calculates if something is True or False
There are seven logical functions: IF, IFERROR, AND, OR, NOT, TRUE, FALSE
Except for IF and IFERROR, logical functions show either TRUE or FALSE as their results
Using the IF Function The result of an IF formula can be a value or a
label The IF function can use arithmetic, relational, or
comparison operators; it has three arguments
Any text must be contained in quotes (“ ”) Arguments are separated by a comma ( , ) The syntax for an IF function is:
=IF(logical_test, value_if_true, value_if_false)
IF Function Arguments
Logical_test is the first argument, the condition. It’s a statement or expression that is either True or False. The expression C5>50 is either true or false, depending on the value in cell C5.
=IF(C5>50,C5*2, "None")
IF Function Arguments
Value_if_true, the second argument, is what the formula shows if the logical_test is TRUE. In the example, if C5 is greater than 50, the value in cell C5 is multiplied by 2. Value_if_true can be a formula, a value, text, or a cell reference.
=IF(C5>50,C5*2, "None")
IF Function Arguments
Value_if_false, the third argument, is what the formula shows if the logical_test is NOT true, if it’s false. Value_if_false can be a formula, a value, text, or a cell reference. In the example, if the value in cell C5 is 50 or less, the result is the word None.
=IF(C5>50,C5*2, "None")
Objective 5
Creating and Editing Cell Styles
21
Creating and Editing Cell Styles
A cell style is a set of formatting attributes for labels or values
A cell style can contain font, number format, border, alignment, fill, and cell protection settings
The default cell style is named Normal
Home Tab, Styles Group Click the Cell Styles button
to see the Cell Styles gallery
The Cell Styles Gallery
Creating and Editing Cell Styles
Create your own cell style by choosing New Cell Style at the bottom of the Cell Styles gallery
Name the style and click Format. Make your choices
Your saved style names appear at the TOP of the gallery
When you edit a style, cells formatted by it are AUTOMATICALLY reformatted
25
Clear Cell Formats
You can reset to the general format by clearing the formats applied
Select cell(s) Home Tab, Editing Group
Click the Clear button
26
Repeat Command
The Repeat Command redoes the most recent action
This command does NOT appear on the ribbon
You can ADD it to the Quick
Access Toolbar or press Ctrl + Y
Objective 6
Using the AND, OR, and NOT Functions
27
Using the AND, OR, and NOT Functions
AND, OR, and NOT are logical functions that show either TRUE or FALSE as the result
They are often used as arguments for other functions (known as “nesting” functions)
They enable you to check for multiple conditions
Using AND
ALL conditions must be met for a “True” result
Very restrictive, less True results For example: Brown hair and
blue eyes
Using OR
In an “OR” function, any one of your logical tests can be true for the result to show “TRUE”
All tests must be false for the result be “FALSE” Less restrictive with more “True” results For Example: Brown hair or blue
eyes
Using NOT
In a “NOT” function, the reverse or opposite of the logical test must be true for the result to show “TRUE”
The “NOT” function has only ONE argument, and it is what you are NOT looking for
Objective 7
Preparing Worksheets for Output
32
Preparing Worksheets for Output
Excel includes several commands to help you prepare a workbook for printing, fore-mailing, or for other forms of electronic distribution
Preparing Worksheets for Output
A page break is a code in a worksheet that tells the printer to start a new page
Excel inserts automatic page breaks based on the paper size, the margins, the column widths, the font size, etc…
You can insert and delete manual page breaks, too
Page Break Preview
Manual page breakSolid lineAutomatic page break
Dashed line
The Breaks Button
The Breaks button is on the Page Layout tab in the Page Setup group
Setting Print Titles Print Titles repeat column or row
labels on each printed sheet Print Titles are typically used for
worksheets that are too big to print on one page
The Print Titles button can befound on the Page Layout tab, in the Page Set-Up group
Changing Margins in Backstage View
Bottom margin
Right margin
Top margin
Left margin
Header margin
Footer margin
Column markers
Creating a Custom Footer
You can set a different header or footer for the first page
Saving a Workbook in Excel 97-2003 Format
After you set the name and folder for saving the workbook, the Compatibility Checker runs.
You can continue as well as copy the summary to a new sheet as documentation.
Saving a Workbook in PDF Format
A PDF is an Adobe portable document file format
A PDF format might be used to distribute the information to customers because PDF’s are easily viewed
To read a PDF file, the receiver needs install Adobe Reader, free software from Adobe Corporation
A PDF uses the file extension .pdf
Saving a Workbook in PDF Format
You can publish the active sheet or the entire workbook as a PDF
Summary
Dates and times are common data types.
Most dates and times are automatically formatted with an appropriate style based on what you key.
Custom date and time formats use codes to specify how the value appears.
Summary
Because of Excel’s serial number system for dates, date and time arithmetic is possible.
Date and Time functions display or convert dates and times into various formats so that the values can be used as needed.
TODAY() and NOW() are volatile functions.
Summary
A cell style is a set of formatting attributes for labels and values.
Cell styles are coordinated with the document theme. They are listed in a gallery and are subject to Live Preview.
You can remove all formatting from a cell and return to the default Normal style.
You can create your own cell styles and save them with the workbook.
Summary
The IF function enables you to create formulas that test whether a condition is true and specify what should be shown or done.
The IF function can show text in its result, it can calculate a value, or it can show a cell reference.
AND, OR, and NOT are logical functions that show either TRUE or FALSE as a result.
Summary
Page breaks determine where a new printed page starts.
Excel inserts automatic page breaks based on the paper size, the margins, font size, and the amount of data.
You can insert and delete your own page breaks.
Page Break Preview shows the page breaks as solid (manual) or dashed (automatic) blue lines.
Summary
If a worksheet requires more than one printed page, use Print Titles to repeat column or row headings on each page.
There are options to set different headers or footers on even and odd pages or on the first printed page.
You can print each page number as well as the total number of pages in a worksheet as a header or a footer.
Summary A workbook can be saved in Excel 97-
2003 format for sharing. The Excel Compatibility Checker finds
features and formats that may not work in Excel 97-2003 format.
A workbook can be saved as a PDF file for viewing by any one who has Adobe Reader.