Learning Microsoft Excel 2007 - Date Calculations

Learning Microsoft Excel 2007 By Greg Bowden Guided Computer Tutorials www.gct.com.au Date Calculations Chapter 18PUBLISHED BY GUIDED COMPUTER TUTORIALS PO Box 311 Belmont, 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 18 Date 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 Load Microsoft Excel or close the current file. 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 and load the file: Library Overdues Selecting YES to the READ-ONLY dialogue box. 3 The template has two worksheets: • the OVERDUES sheet, which will keep track of which books are overdue. • The BOOK LIST sheet, which is a list of the books that the school has. Look at both worksheets.© Guided Computer Tutorials 2007 18-2 Learning Microsoft Excel 2007 Naming the Book List To make the formulas easier to understand the Book List should be named 3 Save the file in your STORAGE folder as: Library Overdues Remember to turn off READ-ONLY RECOMMENDED. 1 Open the BOOK LIST sheet, highlight cells A7 to C16 and use the NAME BOX to name the cells: 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 18 Date Calculations Looking up the Book Title and Author The BOOK TITLE and AUTHOR can be inserted into the OVERDUES sheet using the VLOOKUP command. 1 Return to the OVERDUES sheet. 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. in cell C9: 102.1Far 4 Set the cursor at cell D9 and enter the formula: = IF (C9<>””,VLOOKUP(C9,BOOKS,2),””)© Guided Computer Tutorials 2007 18-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 the formula: = IF (C9<>””,VLOOKUP(C9,BOOKS,3),””) 1 Enter: 28 Jun in cell A9, then set the cursor at cell F9 and enter the formula: = IF (C9<>””,A9+21,0)© Guided Computer Tutorials 2007 18-5 18 Date Calculations 3 Select OK to set the date format. The Overdue Calculation The OVERDUE CHARGE is calculated by a formula that checks whether an accession 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: 2 Aug in cell B5 and name the cell as TODAYS_DATE to make the formula easier to enter.