microsoft® excel 2013. 3 key and format dates and times. 1 use date & time functions. 2 use...

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")


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")


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


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


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


Excel includes several commands to help you prepare a workbook for printing, fore-mailing, or for other forms of electronic distribution


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.

microsoft® excel 2013. 3 key and format dates and times. 1 use date & time functions. 2 use...

Documents