A technician maintains a spreadsheet of laptop loans at a library. Every loan has an issue date in column B, a return date in column C, and a calculated num...
Question 1 Report
A technician maintains a spreadsheet of laptop loans at a library. Every loan has an issue date in column B, a return date in column C, and a calculated number of days borrowed in column D. The library policy is that loans over 14 days must be followed up. The technician also needs the worksheet to show a message instead of an error if a return date has not yet been entered. This information will be used in task B2 before a reminder document is created.
(a) Create a formula that calculates the number of days between C2 and B2. [2]
(b) Create an IF function that displays "Not returned" when C2 is blank; otherwise it calculates the number of days borrowed. [3]
(c) Use a rule to highlight values in column D that are greater than 14. [1]
Answer Details
(a) Use =C2-B2 [2]. Spreadsheet dates are stored as serial values, so subtracting the issue date from the return date gives the number of days borrowed.
(b) Use =IF(C2="","Not returned",C2-B2) [3]. The test checks whether C2 is blank. If it is, the text prevents an inappropriate calculation; otherwise the formula calculates the days.
(c) Apply conditional formatting to column D for values greater than 14 [1]. This highlights loans that require follow-up.
Download The App On Google Playstore
Everything you need to excel in your exams
