Solved

Loan Laptop Database Using Excel 2007 - Identify late laptops and persistant offenders

Posted on 2010-11-27
5
1,166 Views
Last Modified: 2012-05-10
Ive created a Loan Laptop spreadsheet in Excel that shows basically
who loaned the laptop
the from date
the to date
and if it has been returned
.

I created formula that shows clearly when an item is overdue and has not been returned but.....

Is their a way so that if an item is overdue it adds that users name to another sheet and includes the number of days over due and the number of times its has been overdue from that user.

e.g. Fred has borrowed a laptop for 7 days, its now day 10 and it hasnt been returned. He has  done this before but i cant see how many times. Id like the sheet to show that he has not returned the laptop X times to a total of X number of days. Built-PCs---Loan-Laptops.xlsx
0
Comment
Question by:Duco_IT
  • 3
5 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
Look to pivot table in sheet1. You will need additional fields and you fields should be filled with date values.
Built-PCs---Loan-Laptops.xlsx
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Duco_IT,

As you are not recording when a laptop is returned it is not possible to analyse whether they were early, on time or late. At the very least you need a column to record the date on which a laptop is returned.

In fact the 'Returned' column should be used for that rather than recording only whether or not it has been returned. If there's a blank in the Returned column then by implication, the laptop has not been returned. If it has been returned then a date of return is needed.

Patrick
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
Comment Utility
Duco_IT,

In the 'Returned' column in the attached file I have inserted random dates to simulate the dates the laptops were returned.

The code below, also in the attached file produces a report giving you a picture of User's returns' records. Press the button on the Report sheet to obtain the results.

Hope it helps

Patrick
Sub specialmacro()
Dim rng As Range
Dim rng2 As Range
Dim celle As Range
Dim celle2 As Range
Dim coll As New Collection
Dim i As Long
Dim xborrowed As Long
Dim xontime As Long
Dim xlate As Long
Dim dayslate As Long

With Sheets("Loan Laptops")
    Set rng = Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each celle In rng
    If celle.Value > 0 Then
        On Error Resume Next
        coll.Add CStr(celle), CStr(celle)
    End If
Next celle

With Sheets("Report")
    .Cells.ClearContents
    For i = 1 To coll.Count
        .Cells(i + 1, "A") = coll(i)
    Next i
    .Cells(1, "A") = "User"
    .Cells(1, "B") = "Times borrowed"
    .Cells(1, "C") = "Returned on-time"
    .Cells(1, "D") = "Returned late"
    .Cells(1, "E") = "Total days late"
    .Cells(1, "F") = "Ave. days late"
    .Rows(1).Font.Bold = True
    .Columns("A:F").AutoFit
    Set rng2 = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each celle2 In rng2
    For Each celle In rng
        If celle2 = celle Then
            xborrowed = xborrowed + 1
            If celle.Offset(0, 3) <= celle.Offset(0, 2) Then
                xontime = xontime + 1
            Else
                xlate = xlate + 1
                dayslate = dayslate + (celle.Offset(0, 3) - celle.Offset(0, 2))
            End If
        End If
    Next celle
    celle2.Offset(0, 1) = xborrowed
    celle2.Offset(0, 2) = xontime
    celle2.Offset(0, 3) = xlate
    celle2.Offset(0, 4) = dayslate
    celle2.Offset(0, 5) = IIf(dayslate = 0, 0, Int(dayslate / xlate))
    xborrowed = 0
    xontime = 0
    xlate = 0
    dayslate = 0
Next celle2

End Sub

Open in new window

Loan-laptops-01.xls
0
 

Author Closing Comment

by:Duco_IT
Comment Utility
Thanks loads for that
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Duco_IT - Pleased it helped. Thanks for the grade. - Patrick
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Index & Match Help 2 22
Scrape Data from a list of hyperlinks 3 26
Indirect formula 9 28
Excel 2016 Hiding Toolbars 7 10
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now