Learning Microsoft Excel 2007By Greg Bowden

Guided Computer Tutorialswww.gct.com.au

Date CalculationsChapter 18

PUBLISHED BY

GUIDED COMPUTER TUTORIALSPO Box 311Belmont, Victoria, 3216, Australia

www.gct.com.au

© Greg Bowden

This product is available in Single or Multi User versions.

Single-user versions are for single person use at any particular time, just as a single text book would be used. If you intend to use the notes with multiple students the single user version should be upgraded to the multi-user version.

Multi-user versions allow the school or institution to print as many copies as required, or to place the PDF files on the school network, intranet and staff laptops. A certificate of authentication is provided with multi-user versions. Bookmarks provide links to all headings and sub-headings, and individual chapters are provided.

First published 2007

ISBN: 1 921217 44 8 (Module 1) 1 921217 45 6 (Module 2)PDF document on CD-ROM

Every effort has been made to ensure that images used in this publication are free of copyright, but there may be instances where this has not been possible. Guided Computer Tutorials would welcome any information that would redress this situation.

© Guided Computer Tutorials 2007 18-1

Chapter

18Date Calculations

In this chapter you will look at some spreadsheet applications that involve doing calculations on dates. It will involve more detailed IF statements. You will complete prepared templates for a library book overdues system and a debt collection company.

Creating a Library Book Overdue System

A school library needs a simple overdue books table. It should calculate automatically the return date and any overdue fees that need to be charged. Three weeks is the borrowing period after which 5 cents is charged for each day the book is overdue. All the librarian should need to do is enter the Borrow Date, the book’s accession number and the student’s name after which the overdue list should complete itself.

Opening the Prepared Template

1 LoadMicrosoftExcelorclosethecurrentfile. 2 Click on the OPEN icon in the QUICK ACCESS TOOLBAR or in the OFFICE

BUTTON. Access the CHAPTER 18 folder of the EXCEL 2007 SUPPORT FILES andloadthefile:

Library Overdues Selecting YES to the READ-ONLY dialogue box.

3 Thetemplatehastwoworksheets: • theOVERDUESsheet,whichwillkeeptrackofwhichbooksareoverdue. • TheBOOKLISTsheet,whichisalistofthebooksthattheschoolhas. Look at both worksheets.

© Guided Computer Tutorials 200718-2

Learning Microsoft Excel 2007

Naming the Book List

To make the formulas easier to understand the Book List should be named

3 SavethefileinyourSTORAGEfolderas:Library Overdues

Remember to turn off READ-ONLY RECOMMENDED.

1 Open the BOOK LIST sheet, highlight cells A7 to C16 and use theNAMEBOXtonamethecells:

Books

2 Click in one of the BOOK ACCESS NO cells then click on the click on the SORT & FILTER icon in the HOME tab and select SORT A TO Z to sort the cells into BOOK ACCESS. No. order so that lookup formulas can be used on the table.

© Guided Computer Tutorials 2007 18-3

18Date Calculations

Looking up the Book Title and Author

TheBOOKTITLEandAUTHORcanbeinsertedintotheOVERDUESsheetusingtheVLOOKUPcommand.

1 ReturntotheOVERDUESsheet.

3 The Book Title is found by a formula that checks whether a book accession number has been entered, then looks up the BOOKS table and displays the Book Title (COLUMN 2), otherwise a blank is displayed.

2 Enter the test BOOK ACCESS. No. incellC9:

102.1Far

4 Set the cursor at cell D9 and enter theformula:

= IF (C9<>””,VLOOKUP(C9,BOOKS,2),””)

© Guided Computer Tutorials 200718-4

Learning Microsoft Excel 2007

5 The Book Author is obtained through a similar formula to the Book Title, except that COLUMN 3 is used.

Calculating the Return Date

The RETURN DATE is calculated if an accession number has been entered. It is the BORROW DATE plus 21.

NOTE: Because the result is a calculation, use 0 instead of ““ in the OTHERWISE section of your IF formula.

6 Set the cursor at cell E9 and enter theformula:

= IF (C9<>””,VLOOKUP(C9,BOOKS,3),””)

1 Enter:28JunincellA9,thensetthe cursor at cell F9 and enter the formula:

= IF (C9<>””,A9+21,0)

© Guided Computer Tutorials 2007 18-5

18Date Calculations

3 Select OK to set the date format.

The Overdue Calculation

TheOVERDUECHARGEiscalculatedbyaformulathatcheckswhetheranaccession number has been entered and that the difference between TODAY’S DATE and the RETURN DATE is positive (greater than zero). If so, the difference between TODAY’S DATE and the RETURN DATE needs to be multiplied by 5 cents, otherwise zero is displayed.

2 With cell F9 selected, use the NUMBER group arrow to open the FORMAT CELLS dialogue box and format cell F9 to the DATE FORMAT:15-Mar-05

1 Enter:2AugincellB5andnamethe cell as TODAYS_DATE to make the formula easier to enter.