How to get the difference in number of days in excel VBA?

I'm currently working on a library system project using Excel VBA, and I need a module that checks for overdue books, as well as calculate the fines imposed on the user who has an overdue book.

This is the code which I have and it is obviously not working.

Dim fine, count, count2 As Integer
Dim currentdate, borroweddate, difference As String


'this is the amount of fine imposed per day (50 cents)
fine = 1 / 2

'gets number of borrowing records
count = Sheets("borrowing records").Ra开发者_C百科nge("K1").Value

'count2 is just a counter for the cells
count2 = 2

'gets the current date
currentdate = Now()


While (count2 < count + 2)
    If (Sheets("borrowing records").Cells(count2, 8).Value = "ON LOAN") Then
        difference = currentdate - Sheets("borrowing records").Cells(count2, 4).Value

        If (difference > 20) Then
            'prints the overdue record
            Sheets("display").Cells(count2, 1).Value = Sheets("borrowing records").Cells(count2, 1).Value
            Sheets("display").Cells(count2, 2).Value = Sheets("borrowing records").Cells(count2, 2).Value
            Sheets("display").Cells(count2, 3).Value = Sheets("borrowing records").Cells(count2, 3).Value
            Sheets("display").Cells(count2, 4).Value = Sheets("borrowing records").Cells(count2, 4).Value
            Sheets("display").Cells(count2, 5).Value = difference * fine
        End If

    Else
        count2 = count2 + 1

    End If
Wend

Sheets("display").Select

When I tried running a similar code to test for the value of difference, I got random numbers with very long decimals. I don't know why. The results I got weren't even close to the actual difference in days. For example if I test for a difference in days between 20 March and 1 March, I'll get something like 4.35648920486E32E, and yes, there was ever a letter "E" in the result.

My original intention was for the coding to search through a worksheet ("borrowing records"), which is where the program stores records of books being borrowed.

The program would compare the borrow date of the book and the current date, to see if the limit of 20 days is up.

If so, it will calculate the fine by multiplying the date difference by the amount of fine per day (in this case, its 50 cents). The program should then print the overdue records, as well as the fine imposed, in another worksheet ("display").

I know that the program won't work, probably because of the format of the date or something of that sort. However, I have no idea how to solve it.

My project is due on monday and I just hope that someone reading this will help me with it. Thanks!

---

Since we're going on too many comment chains. I thought I'll start a new answer:

For your date difference use DateDiff function like this:

difference = DateDiff("d", currentdate, Sheets("borrowing records").Cells(count2, 4).Value, vbMonday)

where "d" represents the difference is to be provided in number of days and vbMonday tells the system that our week starts on Monday (it defaults to Sunday I think - doubt relevant to you right now but keep in mind for future uses)

This will give you a simple number of days answer which is what you are after. Multiply it with your floating fine value and I think that's all you need for your current problem to solve. No need to go through date formatting as the result will be just a normal number.

---

Dim fine, count, count2 As Integer
Dim currentdate, borroweddate, difference As String

'this is the amount of fine imposed per day (50 cents)
fine = 1 / 2   ' integers don't do floating point!

Have you looked at the value of fine? I think you might find you're under-charging your overdue borrowers ;-)

What is happening here?

difference = currentdate - Sheets("borrowing records").Cells(count2, 4).Value

Try adding a line to see if you're getting the answer you expect, something like

difference = currentdate - Sheets("borrowing records").Cells(count2, 4).Value
Debug.Print currentdate, Sheets("borrowing records").Cells(count2, 4).Value, difference